MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Finding text within text and then parsing out the text after that


Posted by Jonathan McGuire on May 15, 2001 11:31 AM

I have a text value in a cell, for example, 1045/108789, I need to strip out the text to the right of the slash (/). The problem is that the / is not always in the same place. I also need to test to see if the slash (/) does not exist, then return a blank.

Thanks in advance for your help!

Jonathan McGuire


Posted by Mark W. on May 15, 2001 11:38 AM

Do you want to use a formula or an Excel menu
command?

Posted by Jonathan McGuire on May 15, 2001 11:40 AM

Hello Mark!

I would like to use a formula if possible.

Thanks Again,

Jonathatn

Posted by Mark W. on May 15, 2001 11:45 AM

=LEFT(A2,FIND("/",A2))

Posted by Mark W. on May 15, 2001 11:48 AM

Sorry, missed the 2nd requirement...

Use this instead...

=LEFT(A3,IF(ISNUMBER(FIND("/",A3)),FIND("/",A3),))

Posted by Jonathan McGuire on May 15, 2001 11:51 AM

Hello Again Mark!

What I need the formula to do is strip out the remaining text and place it in an adjacent cell. For example, if B2 contains 10456/1104578, in c2 I need the 1104578. The problem is that the slash is not always in the same place. So first I need to search the string to see if contains the / and then parse out the data to the right of the slash. If there is no slash, return a blank.

Thanks for the speedy response.


Jonathan McGuire

Posted by Mark w. on May 15, 2001 11:55 AM

Okay...

I misconstrued "parse out". Use this...

=RIGHT(A2,IF(ISNUMBER(FIND("/",A2)),LEN(A2)-FIND("/",A2),))

Posted by Jonathan McGuire on May 15, 2001 11:58 AM

Re: Okay...

=RIGHT(A2,IF(ISNUMBER(FIND("/",A2)),LEN(A2)-FIND("/",A2),))

Thanks a million Mark! I apologize for not being clear in my initial requests, you know how us users are!

Have a great day,

jonathan