String manipulation

markgordonis · Apr 25, 2011

Suppose the string “Company A Ltd (200012345) – Started 2005” is entered into cell A1 and the string “Company B (SW) Ltd (200012345) – Started 2005” is entered into cell A2.
My objective is to enter into cells B1 and B2 a formula that returns the number 200012345 (i.e. extracts the number from the string, but only extracts the nine digit number, which happens to be a customer number).
So far I’ve used the formula:
=LEFT(RIGHT(A1,LEN(A1)-FIND("(",A1)),9)
This works for company A above, but it does not work for company B. How could I alter this formula so that it works for both companies? Both VBA and function-based solutions very gratefully received.
Thanks in advance
Mark

T. Valko · Apr 25, 2011

markgordonis said:
Suppose the string “Company A Ltd (200012345) – Started 2005” is entered into cell A1 and the string “Company B (SW) Ltd (200012345) – Started 2005” is entered into cell A2.
My objective is to enter into cells B1 and B2 a formula that returns the number 200012345 (i.e. extracts the number from the string, but only extracts the nine digit number, which happens to be a customer number).
So far I’ve used the formula:
=LEFT(RIGHT(A1,LEN(A1)-FIND("(",A1)),9)
This works for company A above, but it does not work for company B. How could I alter this formula so that it works for both companies? Both VBA and function-based solutions very gratefully received.
Thanks in advance
Mark

This worked for me...

=MID(A1,FIND(CHAR(150),A1)-11,9)

T. Valko · Apr 25, 2011

T. Valko said:
This worked for me...

=MID(A1,FIND(CHAR(150),A1)-11,9)

Note that this formula returns the result as a TEXT string.

poolhall · Apr 25, 2011

=left(replace(a1,1,lookup(9.99e+307,find("(",a1,row($1:$1024))),""),9)

markgordonis · Apr 25, 2011

Thanks poolhall and T Valko!

This works

Rick Rothstein · Apr 25, 2011

Here is a UDF (user defined function) that should work no matter what other text is in your cells...

Code:

Function CustomerNumber(S As String) As Long
  Dim X As Long
  For X = 1 To Len(S) - 8
    If Mid(S, X) Like "(#########)*" Then
      CustomerNumber = Mid(S, X + 1, 9)
      Exit For
    End If
  Next
End Function

To use this UDF, just put this formula in B1 and copy it down as needed...

=CustomerNumber(A1)

If you have never installed a UDF before, it is quite easy to do... just press ALT+F11 to go into the VB editor, click Insert/Module once there and copy/paste the above function code into the code window that opened up. That's it, the UDF is installed and ready for use.

T. Valko · Apr 25, 2011

markgordonis said:
Thanks poolhall and T Valko!

This works

You're welcome. Thanks for the feedback!

markgordonis · Apr 25, 2011

Thanks for UDF Rick!

String manipulation

markgordonis

New Member

Excel Facts

T. Valko

Well-known Member

T. Valko

Well-known Member

poolhall

Active Member

markgordonis

New Member

Rick Rothstein

MrExcel MVP

T. Valko

Well-known Member

markgordonis

New Member

Similar threads

Forum statistics

Share this page

String manipulation

New Member

Excel Facts

Well-known Member

Well-known Member

Active Member

New Member

MrExcel MVP

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock