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

Question

I have a column like this "2;#Vendor", I want to display only "Vendor".

How can I do this?

Answer 1

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

Answer 2

cell a1: 2;#Vendor

cell b1: +Find("Vendor",a1,1)

c1 =+MID(A1,B1,LEN(A1))

=========== Or ================

2;#Vendor

B1 =+MID(A1,Find("Vendor",a1,1),LEN(A1))