Remove and Add Text to Numbers
 

I have about 600 rows that have a distinct combination of text and numbers

example:

P10062 - US-Executive Management
P10198 - MSN PUBLISHING PLATFORM
P10294 - MSN OPS - CS Hosting Charges
P10363 - MSNIA Service Engineering
P10434 - MSN MEDIA NETWORK MGMT
P10435 - GBI
P10963 - US-CCG Ops Management
P10964 - Tier 1 Redmond
P10965 - Service Ops Mgmt-Redmond
P10971 - Entertainment COGS
P10972 - MSN Portal COGS

I need to remove all text except for the number but, since all 600 lines are
different, a simple find and replace wont work.

I will also need to do the opposite and add the text to the number and as
above a simple find and replace wont work.

Is there a function in Exel that can do this or a simple macro?

Remove and Add Text to Numbers
 

I have about 600 rows that have a distinct combination of text and numbers

example:

P10062 - US-Executive Management
P10198 - MSN PUBLISHING PLATFORM
P10294 - MSN OPS - CS Hosting Charges
P10363 - MSNIA Service Engineering
P10434 - MSN MEDIA NETWORK MGMT
P10435 - GBI
P10963 - US-CCG Ops Management
P10964 - Tier 1 Redmond
P10965 - Service Ops Mgmt-Redmond
P10971 - Entertainment COGS
P10972 - MSN Portal COGS

I need to remove all text except for the number but, since all 600 lines
are
different, a simple find and replace wont work.

I will also need to do the opposite and add the text to the number and as
above a simple find and replace wont work.

Is there a function in Exel that can do this or a simple macro?

If your entries above are in col A from row 1, put this formula in col B and
copy down:
=MID(A1,2,FIND(" ",A1)-2)*1

and this one in col C and copy down:
=MID(A1,FIND("-",A1)+2,LEN(A1)-FIND("-",A1)-1)

I assume there's always one letter before the number, and the text always
follows " - "?

Remove and Add Text to Numbers
 

This will extract the number in a worksheet
=VALUE(MID(A1,2,FIND(" ",A1)-1))

for VBA
MyString
VAL(mid(MyString,2,Instr(MyStr," ")-1))

"Jason Hall" wrote:

I have about 600 rows that have a distinct combination of text and numbers

example:

P10062 - US-Executive Management
P10198 - MSN PUBLISHING PLATFORM
P10294 - MSN OPS - CS Hosting Charges
P10363 - MSNIA Service Engineering
P10434 - MSN MEDIA NETWORK MGMT
P10435 - GBI
P10963 - US-CCG Ops Management
P10964 - Tier 1 Redmond
P10965 - Service Ops Mgmt-Redmond
P10971 - Entertainment COGS
P10972 - MSN Portal COGS

I need to remove all text except for the number but, since all 600 lines are
different, a simple find and replace wont work.

I will also need to do the opposite and add the text to the number and as
above a simple find and replace wont work.

Is there a function in Exel that can do this or a simple macro?

Remove and Add Text to Numbers
 

for VBA
MyString
VAL(mid(MyString,2,Instr(MyStr," ")-1))

Just to point out to the OP... I'm guessing you meant to write MyString
whereas you wrote MyStr instead.

By the way, here is another what to get the number out in VBA...

--Mid(Split(MyString)(0),2)

Although if the "code number" in the front is always constructed as shown,
you could get the number portion directly....

--Mid(MyString,2,5)

Rick