String Manipulation

poleary2000 · Apr 9th, 2002, 07:00 AM

All,

I have the following situation.

I have ranges named the following:

2002.03.01!Monday2
2002.03.01!Thursday
2002.03.01!Friday
2002.03.01!SaturdayWO2
etc.

Is there anyway I can capture all information to the right of the ! and set it equal to a variable? I have been trying Left and Right with no success.

Andrew70 · Apr 9th, 2002, 07:34 AM

If your data is in cell A1,

=MID(A1,FIND("!",A1)+1,1000)

this will isolate everything after the !.
You can then refer to this result in other formulas.
(If the data after the ! is likely to be longer than 1000 characters, increase the number)

poleary2000 · Apr 9th, 2002, 07:37 AM

Thanks! I was actually trying just that!

Peo08 · Apr 9th, 2002, 07:48 AM

Quote:

On 2002-04-09 06:37, poleary2000 wrote:
Thanks! I was actually trying just that!

How about using data>text to columns, select delimited, click next and as delimiter choose
other and put an Exclamation mark in the other box, click next, click in the left column, select "do not import column (skip)", click Finish..

No need for formulas

Regards,

Peo Sjoblom

poleary2000 · Apr 9th, 2002, 08:07 AM

Actually, I need to do this with VBA. Neither method works. Is there a VBA function that can help?

poleary2000 · Apr 9th, 2002, 08:09 AM

Also, what happens if the ! is not found? Can I make it go to another part of the program?

Apr 9th, 2002, 07:00 AM	#1
poleary2000 Board Regular Join Date: Apr 2002 Location: Pittsburgh, PA Posts: 317	All, I have the following situation. I have ranges named the following: 2002.03.01!Monday2 2002.03.01!Thursday 2002.03.01!Friday 2002.03.01!SaturdayWO2 etc. Is there anyway I can capture all information to the right of the ! and set it equal to a variable? I have been trying Left and Right with no success. __________________ Thanks in advance, Patrick

Apr 9th, 2002, 07:37 AM	#3
poleary2000 Board Regular Join Date: Apr 2002 Location: Pittsburgh, PA Posts: 317	Thanks! I was actually trying just that! __________________ Thanks in advance, Patrick

Apr 9th, 2002, 08:07 AM	#5
poleary2000 Board Regular Join Date: Apr 2002 Location: Pittsburgh, PA Posts: 317	Actually, I need to do this with VBA. Neither method works. Is there a VBA function that can help? __________________ Thanks in advance, Patrick

Apr 9th, 2002, 08:09 AM	#6
poleary2000 Board Regular Join Date: Apr 2002 Location: Pittsburgh, PA Posts: 317	Also, what happens if the ! is not found? Can I make it go to another part of the program? __________________ Thanks in advance, Patrick

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 07:34 AM	#2
Andrew70 New Member Join Date: Apr 2002 Posts: 30	If your data is in cell A1, =MID(A1,FIND("!",A1)+1,1000) this will isolate everything after the !. You can then refer to this result in other formulas. (If the data after the ! is likely to be longer than 1000 characters, increase the number)