| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find character in a string
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find character in a string
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)) -- Gary "Mike" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find character in a string
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 "Gary Keramidas" wrote: 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)) -- Gary "Mike" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find character in a string
On Wed, 20 Feb 2008 18:44:00 -0800, Mike
wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find character in a string
Ron I'm getting the value sign and I for sure that there is the "#" in the cell
"Ron Rosenfeld" wrote: On Wed, 20 Feb 2008 18:44:00 -0800, Mike wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find character in a string
On Wed, 20 Feb 2008 19:51:01 -0800, Mike
wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find character in a string
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&"01234 56789")),99)*1 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Mike" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| FIND 2nd character in a string | Excel Worksheet Functions | |||
| find a character in a string | Excel Discussion (Misc queries) | |||
| Find Character Position in String | Excel Programming | |||
| Find nth instance of a character in a string | Excel Discussion (Misc queries) | |||
| backwards find function to find character in a string of text | Excel Programming | |||
Hybrid Mode
