Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

How can I do this?

share|improve this question

2 Answers 2

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

share|improve this answer

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))

share|improve this answer
    
Fathoming out what is being asked is often not easy but in this case it seems (to me) that ‘Vendor’ can’t be taken literally – that it is a generic representation of some more specific name – so finding exactly that does not look relevant to me (might almost just as well delete the first 3 characters or, to allow for other than 3 (as you have) use =RIGHT(A1,6)). Also + is not necessary as well as ‘=’ here. –  pnuts Oct 30 '12 at 2:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.