Hide a number that comes before the text of a column in Excel?

You could either split this column into two, using the "Text to Columns" function of the Data tab in the ribbon menu. You would just have to select # as the separator.

Or, if your example meant already two separate columns and # was a placeholder for a number, like this:

2 | 12Vendor
3 | 145Vendor

Then you could get the Vendor out, using a VBA formula, which would have to parse the input.

Except of course, if Vendor or the number before Vendor follows some specific rules, which you might tap into - like a fixed size. If you have always a format like 001Vendor then you can use this formula:

=RIGHT(A1,LENGTH(A1)-3)

Edit:

Here is a nice solution, that you could use as a worksheet function:

Public Function demo(ByRef rng As Range) As String

    Dim objRegEx As Object
    Set objRegEx = CreateObject("VBscript.regexp")

    objRegEx.IgnoreCase = True
    objRegEx.Global = True
    objRegEx.MultiLine = True
    objRegEx.Pattern = "\d" 'Match any digit. Equivalent to [0-9].

    demo = objRegEx.Replace(rng.Value, "")
    'The Replace method takes 2 strings as its arguments.
    'If it is able to successfully match the regular expression
    'in the search-string, then it replaces that match with the
    'replace-string, and the new string is returned.
    'If no matches were found, then the original search-string is returned.

    Set objRegEx = Nothing

End Function

It is using regular expression, which you could use on many other occasions, just by making the pattern dynamic.

Here is some documentation on this: http://msdn.microsoft.com/en-us/library/ms974570.aspx

And to be fair - I just adapted an example, which I found here:

http://www.office-loesung.de/ftopic134495_0_0_asc.php

Jook

Posted 2012-10-29T09:23:49.153

Reputation: 1 745

Hide a number that comes before the text of a column in Excel?

Answers