Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract text string using MID
Dear all,
I have got a text string as below She is a girl,,,,,"I am a boy","" What the formula should be to extract the string between ,,,,," and ","" so that the result comes out with I am a boy ? Thanks Turk |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract text string using MID
One way:
=SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("""",A1)+1,255) ,"""",""),",","") Biff "Turk" wrote in message ... Dear all, I have got a text string as below She is a girl,,,,,"I am a boy","" What the formula should be to extract the string between ,,,,," and ","" so that the result comes out with I am a boy ? Thanks Turk |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract text string using MID
Dear Biff,
Thanks for your prompt response. However the formula doesn't work when there are some more characters follow the strings ie: She is a girl,,,,,"I am a boy",""she is a woman returns I am a boyshe is a woman but I only want the result to be I am a boy Pls help Turk "Biff" .gbl... One way: =SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("""",A1)+1,255) ,"""",""),",","") Biff "Turk" wrote in message ... Dear all, I have got a text string as below She is a girl,,,,,"I am a boy","" What the formula should be to extract the string between ,,,,," and ","" so that the result comes out with I am a boy ? Thanks Turk |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract text string using MID
Try this:
=LEFT(MID(A1,FIND("""",A1)+1,255),FIND(",",MID(A1, FIND("""",A1)+1,255))-2) I'm assuming every entry has as part of the string: .........."some text",.............. If not then post SEVERAL representative samples so we can see what's needed. Biff "Turk" wrote in message ... Dear Biff, Thanks for your prompt response. However the formula doesn't work when there are some more characters follow the strings ie: She is a girl,,,,,"I am a boy",""she is a woman returns I am a boyshe is a woman but I only want the result to be I am a boy Pls help Turk "Biff" .gbl... One way: =SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("""",A1)+1,255) ,"""",""),",","") Biff "Turk" wrote in message ... Dear all, I have got a text string as below She is a girl,,,,,"I am a boy","" What the formula should be to extract the string between ,,,,," and ","" so that the result comes out with I am a boy ? Thanks Turk |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract text string using MID
Dear Biff,
This works, thank you~ Turk "Biff" l... Try this: =LEFT(MID(A1,FIND("""",A1)+1,255),FIND(",",MID(A1, FIND("""",A1)+1,255))-2) I'm assuming every entry has as part of the string: ........."some text",.............. If not then post SEVERAL representative samples so we can see what's needed. Biff "Turk" wrote in message ... Dear Biff, Thanks for your prompt response. However the formula doesn't work when there are some more characters follow the strings ie: She is a girl,,,,,"I am a boy",""she is a woman returns I am a boyshe is a woman but I only want the result to be I am a boy Pls help Turk "Biff" .gbl... One way: =SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("""",A1)+1,255) ,"""",""),",","") Biff "Turk" wrote in message ... Dear all, I have got a text string as below She is a girl,,,,,"I am a boy","" What the formula should be to extract the string between ,,,,," and ","" so that the result comes out with I am a boy ? Thanks Turk |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract text string using MID
You're welcome. Thanks for the feedback!
Biff "Turk" wrote in message ... Dear Biff, This works, thank you~ Turk "Biff" l... Try this: =LEFT(MID(A1,FIND("""",A1)+1,255),FIND(",",MID(A1, FIND("""",A1)+1,255))-2) I'm assuming every entry has as part of the string: ........."some text",.............. If not then post SEVERAL representative samples so we can see what's needed. Biff "Turk" wrote in message ... Dear Biff, Thanks for your prompt response. However the formula doesn't work when there are some more characters follow the strings ie: She is a girl,,,,,"I am a boy",""she is a woman returns I am a boyshe is a woman but I only want the result to be I am a boy Pls help Turk "Biff" .gbl... One way: =SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("""",A1)+1,255) ,"""",""),",","") Biff "Turk" wrote in message ... Dear all, I have got a text string as below She is a girl,,,,,"I am a boy","" What the formula should be to extract the string between ,,,,," and ","" so that the result comes out with I am a boy ? Thanks Turk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract specific value from a long text string | Excel Worksheet Functions | |||
Formula to extract digits from a text string? | Excel Worksheet Functions | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) | |||
EXTRACT TEXT FROM TEXT STRING | Excel Worksheet Functions | |||
Extracting from a text string | Excel Worksheet Functions |