Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract one character
Column C in one worksheet has text strings, from Row 2 thru Row 676. These
strings can be two, three, or four words. I need a formula to extract the first character to the right of the last space. For example, Rts is unpaid - u Ldpq to be quoted - q Will call - c Thanks for the help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract one character
One way is to use a helper column and a formula like this:
=LEFT(TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),1) and drag down Carl wrote: Column C in one worksheet has text strings, from Row 2 thru Row 676. These strings can be two, three, or four words. I need a formula to extract the first character to the right of the last space. For example, Rts is unpaid - u Ldpq to be quoted - q Will call - c Thanks for the help -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract one character
Thank you, works like a charm.
Now I need to figure out how it does the job??? "Dave Peterson" wrote: One way is to use a helper column and a formula like this: =LEFT(TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),1) and drag down Carl wrote: Column C in one worksheet has text strings, from Row 2 thru Row 676. These strings can be two, three, or four words. I need a formula to extract the first character to the right of the last space. For example, Rts is unpaid - u Ldpq to be quoted - q Will call - c Thanks for the help -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract one character
if c2 holds
abc def then =SUBSTITUTE(C2," ",REPT(" ",99)) would look like: abc(99 space characters)def So =RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)) would give the right most 99 characters: (96 space characters)def (96 since def is just 3 characters) =trim() removes the leading/trailing (and duplicated internal spaces!) in the string: so =TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)) is just def and =left(..., 1) returns the first character of that def (just d). I didn't come up with that =substitute() trick, but it works very nice under certain conditions. Carl wrote: Thank you, works like a charm. Now I need to figure out how it does the job??? "Dave Peterson" wrote: One way is to use a helper column and a formula like this: =LEFT(TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),1) and drag down Carl wrote: Column C in one worksheet has text strings, from Row 2 thru Row 676. These strings can be two, three, or four words. I need a formula to extract the first character to the right of the last space. For example, Rts is unpaid - u Ldpq to be quoted - q Will call - c Thanks for the help -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract one character
Thank you very much for the time spent in explaining the solution.
As always you efforts are greatly appreciated. "Dave Peterson" wrote: if c2 holds abc def then =SUBSTITUTE(C2," ",REPT(" ",99)) would look like: abc(99 space characters)def So =RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)) would give the right most 99 characters: (96 space characters)def (96 since def is just 3 characters) =trim() removes the leading/trailing (and duplicated internal spaces!) in the string: so =TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)) is just def and =left(..., 1) returns the first character of that def (just d). I didn't come up with that =substitute() trick, but it works very nice under certain conditions. Carl wrote: Thank you, works like a charm. Now I need to figure out how it does the job??? "Dave Peterson" wrote: One way is to use a helper column and a formula like this: =LEFT(TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),1) and drag down Carl wrote: Column C in one worksheet has text strings, from Row 2 thru Row 676. These strings can be two, three, or four words. I need a formula to extract the first character to the right of the last space. For example, Rts is unpaid - u Ldpq to be quoted - q Will call - c Thanks for the help -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract numeric characters plus one character... | Excel Worksheet Functions | |||
Extract a text string based on character | Excel Worksheet Functions | |||
Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
extract data up to a certain character | Excel Worksheet Functions | |||
Extract Unique Values, Then Extract Again to Remove Suffixes | Excel Discussion (Misc queries) |