| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to return part of a text string
I have a clomun of cells with various codes in e.g:
Column A 42NAFF16-43 927PLY833-16FF 16ARBY1-1 etc.. The only common factor in all these references is the dash symbol. I am looking to return in another column just the figures after the dash e.g: Column B 43 16FF 1 I'm stuck as to what formula to use as I'm only used to chopping off a fixed number of digits! Any help is greatly appreciated. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to return part of a text string
With your data in column A, put this in B1 ad copy down........
=MID(A1,FIND("-",A1,1),99) Vaya con Dios, Chuck, CABGx3 "luvthavodka" wrote: I have a clomun of cells with various codes in e.g: Column A 42NAFF16-43 927PLY833-16FF 16ARBY1-1 etc.. The only common factor in all these references is the dash symbol. I am looking to return in another column just the figures after the dash e.g: Column B 43 16FF 1 I'm stuck as to what formula to use as I'm only used to chopping off a fixed number of digits! Any help is greatly appreciated. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to return part of a text string
If the hyphen appears only once in the text, then use this:
=RIGHT(B2,LEN(B2)-FIND("-",B2)) Hope this helps, Dom luvthavodka wrote: I have a clomun of cells with various codes in e.g: Column A 42NAFF16-43 927PLY833-16FF 16ARBY1-1 etc.. The only common factor in all these references is the dash symbol. I am looking to return in another column just the figures after the dash e.g: Column B 43 16FF 1 I'm stuck as to what formula to use as I'm only used to chopping off a fixed number of digits! Any help is greatly appreciated. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to return part of a text string
Thanks for this, however it is returning -43GF, rather than 43GF.
How can I expand on this formula to also remove the "-"? Many thanks "CLR" wrote: With your data in column A, put this in B1 ad copy down........ =MID(A1,FIND("-",A1,1),99) Vaya con Dios, Chuck, CABGx3 "luvthavodka" wrote: I have a clomun of cells with various codes in e.g: Column A 42NAFF16-43 927PLY833-16FF 16ARBY1-1 etc.. The only common factor in all these references is the dash symbol. I am looking to return in another column just the figures after the dash e.g: Column B 43 16FF 1 I'm stuck as to what formula to use as I'm only used to chopping off a fixed number of digits! Any help is greatly appreciated. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to return part of a text string
Sorry, this should do it for you.........
=MID(A1,FIND("-",A1,1)+1,99) Vaya con Dios, Chuck, CABGx3 "luvthavodka" wrote: Thanks for this, however it is returning -43GF, rather than 43GF. How can I expand on this formula to also remove the "-"? Many thanks "CLR" wrote: With your data in column A, put this in B1 ad copy down........ =MID(A1,FIND("-",A1,1),99) Vaya con Dios, Chuck, CABGx3 "luvthavodka" wrote: I have a clomun of cells with various codes in e.g: Column A 42NAFF16-43 927PLY833-16FF 16ARBY1-1 etc.. The only common factor in all these references is the dash symbol. I am looking to return in another column just the figures after the dash e.g: Column B 43 16FF 1 I'm stuck as to what formula to use as I'm only used to chopping off a fixed number of digits! Any help is greatly appreciated. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to return part of a text string
Hi,
This is helpful, but have something slightly different i could use some help with. Two different situations. One, is there a way to combine this with the "LEFT" Command? In my example, i am looking to get the city and state, but not include the dash. The dash is my common separater in all my values. City, ST - Market Number Looking to Return "City, St" In my second scenario, I want to use the same command except have it find the second dash and return everything to the left of the second dash. WWWW-FM-City, St Looking to return "WWWW-FM" In both situations, i can't use the straight LEFT Command because the list has different number of characters. I might be able to easily replace the second dash with a different common character, but this formula includes the common character and i would prefer not to see it. Thanks - Any help is appreciated. Rugdoody "CLR" wrote: Sorry, this should do it for you......... =MID(A1,FIND("-",A1,1)+1,99) Vaya con Dios, Chuck, CABGx3 "luvthavodka" wrote: Thanks for this, however it is returning -43GF, rather than 43GF. How can I expand on this formula to also remove the "-"? Many thanks "CLR" wrote: With your data in column A, put this in B1 ad copy down........ =MID(A1,FIND("-",A1,1),99) Vaya con Dios, Chuck, CABGx3 "luvthavodka" wrote: I have a clomun of cells with various codes in e.g: Column A 42NAFF16-43 927PLY833-16FF 16ARBY1-1 etc.. The only common factor in all these references is the dash symbol. I am looking to return in another column just the figures after the dash e.g: Column B 43 16FF 1 I'm stuck as to what formula to use as I'm only used to chopping off a fixed number of digits! Any help is greatly appreciated. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to return part of a text string
For your first scenario
=LEFT(A1,FIND("-",A1)-1) and for your second =LEFT(A2,FIND("^^",SUBSTITUTE(A2,"-","^^",2))-1) -- Regards, Peo Sjoblom -- Regards, Peo Sjoblom "Rugdoody" wrote in message ... Hi, This is helpful, but have something slightly different i could use some help with. Two different situations. One, is there a way to combine this with the "LEFT" Command? In my example, i am looking to get the city and state, but not include the dash. The dash is my common separater in all my values. City, ST - Market Number Looking to Return "City, St" In my second scenario, I want to use the same command except have it find the second dash and return everything to the left of the second dash. WWWW-FM-City, St Looking to return "WWWW-FM" In both situations, i can't use the straight LEFT Command because the list has different number of characters. I might be able to easily replace the second dash with a different common character, but this formula includes the common character and i would prefer not to see it. Thanks - Any help is appreciated. Rugdoody "CLR" wrote: Sorry, this should do it for you......... =MID(A1,FIND("-",A1,1)+1,99) Vaya con Dios, Chuck, CABGx3 "luvthavodka" wrote: Thanks for this, however it is returning -43GF, rather than 43GF. How can I expand on this formula to also remove the "-"? Many thanks "CLR" wrote: With your data in column A, put this in B1 ad copy down........ =MID(A1,FIND("-",A1,1),99) Vaya con Dios, Chuck, CABGx3 "luvthavodka" wrote: I have a clomun of cells with various codes in e.g: Column A 42NAFF16-43 927PLY833-16FF 16ARBY1-1 etc.. The only common factor in all these references is the dash symbol. I am looking to return in another column just the figures after the dash e.g: Column B 43 16FF 1 I'm stuck as to what formula to use as I'm only used to chopping off a fixed number of digits! Any help is greatly appreciated. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to return part of a text string
=LEFT(F1,FIND("-",F1,1)-1) for the first case
=LEFT(F7,FIND("-",F7,FIND("-",F7,1)+1)-1) for the second Vaya con Dios, Chuck, CABGx3 "Rugdoody" wrote: Hi, This is helpful, but have something slightly different i could use some help with. Two different situations. One, is there a way to combine this with the "LEFT" Command? In my example, i am looking to get the city and state, but not include the dash. The dash is my common separater in all my values. City, ST - Market Number Looking to Return "City, St" In my second scenario, I want to use the same command except have it find the second dash and return everything to the left of the second dash. WWWW-FM-City, St Looking to return "WWWW-FM" In both situations, i can't use the straight LEFT Command because the list has different number of characters. I might be able to easily replace the second dash with a different common character, but this formula includes the common character and i would prefer not to see it. Thanks - Any help is appreciated. Rugdoody "CLR" wrote: Sorry, this should do it for you......... =MID(A1,FIND("-",A1,1)+1,99) Vaya con Dios, Chuck, CABGx3 "luvthavodka" wrote: Thanks for this, however it is returning -43GF, rather than 43GF. How can I expand on this formula to also remove the "-"? Many thanks "CLR" wrote: With your data in column A, put this in B1 ad copy down........ =MID(A1,FIND("-",A1,1),99) Vaya con Dios, Chuck, CABGx3 "luvthavodka" wrote: I have a clomun of cells with various codes in e.g: Column A 42NAFF16-43 927PLY833-16FF 16ARBY1-1 etc.. The only common factor in all these references is the dash symbol. I am looking to return in another column just the figures after the dash e.g: Column B 43 16FF 1 I'm stuck as to what formula to use as I'm only used to chopping off a fixed number of digits! Any help is greatly appreciated. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to return part of a text string
Hi
For the first question =LEFT(A1,FIND("-",A1)-1) For the second =LEFT(A1,FIND("*",SUBSTITUTE(A1,"-","*",2))-1) -- Regards Roger Govier "Rugdoody" wrote in message ... Hi, This is helpful, but have something slightly different i could use some help with. Two different situations. One, is there a way to combine this with the "LEFT" Command? In my example, i am looking to get the city and state, but not include the dash. The dash is my common separater in all my values. City, ST - Market Number Looking to Return "City, St" In my second scenario, I want to use the same command except have it find the second dash and return everything to the left of the second dash. WWWW-FM-City, St Looking to return "WWWW-FM" In both situations, i can't use the straight LEFT Command because the list has different number of characters. I might be able to easily replace the second dash with a different common character, but this formula includes the common character and i would prefer not to see it. Thanks - Any help is appreciated. Rugdoody "CLR" wrote: Sorry, this should do it for you......... =MID(A1,FIND("-",A1,1)+1,99) Vaya con Dios, Chuck, CABGx3 "luvthavodka" wrote: Thanks for this, however it is returning -43GF, rather than 43GF. How can I expand on this formula to also remove the "-"? Many thanks "CLR" wrote: With your data in column A, put this in B1 ad copy down........ =MID(A1,FIND("-",A1,1),99) Vaya con Dios, Chuck, CABGx3 "luvthavodka" wrote: I have a clomun of cells with various codes in e.g: Column A 42NAFF16-43 927PLY833-16FF 16ARBY1-1 etc.. The only common factor in all these references is the dash symbol. I am looking to return in another column just the figures after the dash e.g: Column B 43 16FF 1 I'm stuck as to what formula to use as I'm only used to chopping off a fixed number of digits! Any help is greatly appreciated. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to return part of a text string
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find $ in a string of text and return numbers | Excel Discussion (Misc queries) | |||
| Finding specific text in string - Part II | Excel Worksheet Functions | |||
| Text Wrapping Cells that are part of a formula | Excel Discussion (Misc queries) | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formulas dealing with text data | Excel Worksheet Functions | |||
Linear Mode
