Find character in a string

[Mike]

Thanks for any help.
How can I get Everytthing to the left of a character.
Examples od data
Tobacco Outlet #1
TRO #1
Tobacco Road Outlet #1
Needs to return 1
Tobacco Outlet #1201
TRO #1201
Tobacco Road Outlet #1201
Needs to return 1201

 

[Gary Keramidas]

you don't mention if you have need for a formula or a macro:

here's a formula, write back with more info about the sheet layout if you
want code.

=RIGHT(A1,LEN(A1)-FIND("#",A1))

 

[Ron Coderre]

With
A1: (a text value ending in a number.....eg Tobacco Outlet #1201)

This formula returns the numbers:
B1: =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)*1

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Mike]

Gary
For some reason my messages are getting an error so no telling how many
replies will show up.
I get an #value! with the formula
Sorry so short on prior post

I will be passing the value to a ADODB.RECORDSET

 

[Ron Rosenfeld]

Gary
For some reason my messages are getting an error so no telling how many
replies will show up.
I get an #value! with the formula
Sorry so short on prior post

I will be passing the value to a ADODB.RECORDSET

You will get a #VALUE! error if the "#" token is not in the string being
processed.
--ron

 

[Mike]

Ron I'm getting the value sign and I for sure that there is the "#" in the cell

 

[Ron Rosenfeld]

Ron I'm getting the value sign and I for sure that there is the "#" in the cell

Copy/paste a cell with your data that is returning the #VALUE error, and also
copy/paste the formula you are using that is returning that error to a message
here, so we can see exactly what is there with no possible typos.

If the cell with the data is from a formula, post that here, too.

Were you able to use the "trace error" option from the little button that might
show up when you select the cell showing the error?
--ron