| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract portion of cell contents
Have a text string that could look something like this:
http://he4.clientthirddheytrs.com/wyt/cftts/edit.aspx?id={840102F6-AJUE-SD1B-9705-00188B2E03D9} I need to extract the ID number which is surrounded by "the brackets". Desired result in this instance would be: {840102F6-AJUE-SD1B-9705-00188B2} There is no consistency in the data; there can be any number of characters between the brackets, and any number of characters before, or after the brackets. It's all formatted as text. Ideas? Thanks in advance. Rick |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract portion of cell contents
Assuming the ID always comes at the end of the text (as you example
shows)... =MID(A1,FIND("=",A1)+1,99) -- Rick (MVP - Excel) "Rick" wrote in message ... Have a text string that could look something like this: http://he4.clientthirddheytrs.com/wyt/cftts/edit.aspx?id={840102F6-AJUE-SD1B-9705-00188B2E03D9} I need to extract the ID number which is surrounded by "the brackets". Desired result in this instance would be: {840102F6-AJUE-SD1B-9705-00188B2} There is no consistency in the data; there can be any number of characters between the brackets, and any number of characters before, or after the brackets. It's all formatted as text. Ideas? Thanks in advance. Rick |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract portion of cell contents; (follow up)
On Dec 17, 4:32*pm, "Rick Rothstein"
wrote: Assuming the ID always comes at the end of the text (as you example shows)... =MID(A1,FIND("=",A1)+1,99) -- Rick (MVP - Excel) "Rick" wrote in message ... Have a text string that could look something like this: http://he4.clientthirddheytrs.com/wyt/cftts/edit.aspx?id={840102F6-AJUE-SD1B-9705-00188B2E03D9} I need to extract the ID number which is surrounded by "the brackets". Desired result in this instance would be: {840102F6-AJUE-SD1B-9705-00188B2} There is no consistency in the data; there can be any number of characters between the brackets, and any number of characters before, or after the brackets. *It's all formatted as text. Ideas? Thanks in advance. Rick- Hide quoted text - - Show quoted text - Rick, Oops. There will be instances where characters will appear after the last bracket. I appologize for the poor example. Those need to be stripped off as well. Want to take another run at it? Thanks again for your assistance. Rick |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract portion of cell contents; (follow up)
On Dec 18, 5:52*am, Rick wrote:
On Dec 17, 4:32*pm, "Rick Rothstein" wrote: Assuming the ID always comes at the end of the text (as you example shows)... =MID(A1,FIND("=",A1)+1,99) -- Rick (MVP - Excel) "Rick" wrote in message Rick: found one that works after poking around the archives for similar formulas,a dn utilizing your formula and inspiration. . . =LEFT(MID(A1,FIND("=",A1)+1,99),FIND("#",MID(A1,FI ND("=",A1)+1,99))-1) First bracket is always preceeded by a =, and last bracked is followed by a #. Many many thanks. Rick |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract portion of cell contents
Hi Rick,
Here's one more just for fun. =MID(A4,FIND("{",A4)+1,FIND("}",A4)-FIND("{",A4)-1) HTH Regards, Howard "Rick" wrote in message ... Have a text string that could look something like this: http://he4.clientthirddheytrs.com/wyt/cftts/edit.aspx?id={840102F6-AJUE-SD1B-9705-00188B2E03D9} I need to extract the ID number which is surrounded by "the brackets". Desired result in this instance would be: {840102F6-AJUE-SD1B-9705-00188B2} There is no consistency in the data; there can be any number of characters between the brackets, and any number of characters before, or after the brackets. It's all formatted as text. Ideas? Thanks in advance. Rick |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract portion of cell contents; (follow up)
Rik
I would suggest the following amendment as an enhanced version of the formula =LEFT(MID(A1,FIND("{",A1),LEN(A1)),FIND("}",MID(A1 ,FIND("{",A1),LEN(A1)))) this simplifies things slightly, and in your original post you said the number of characters between the brackets is not fixed... so this removes the limitation of 99 characters, as you have currently got. Hope this is ok... -- If this is the answer has helped please remember to click the yes button below... Kind regards Rik "Rick" wrote: On Dec 18, 5:52 am, Rick wrote: On Dec 17, 4:32 pm, "Rick Rothstein" wrote: Assuming the ID always comes at the end of the text (as you example shows)... =MID(A1,FIND("=",A1)+1,99) -- Rick (MVP - Excel) "Rick" wrote in message Rick: found one that works after poking around the archives for similar formulas,a dn utilizing your formula and inspiration. . . =LEFT(MID(A1,FIND("=",A1)+1,99),FIND("#",MID(A1,FI ND("=",A1)+1,99))-1) First bracket is always preceeded by a =, and last bracked is followed by a #. Many many thanks. Rick . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract portion of cell contents; (follow up)
Or event this approach using the two curly brackets as you suggest, but
using a simpler construction than the OP's found solution (still using MID, but using a much larger number of character)... =MID(LEFT(A1,FIND("}",A1)),FIND("{",A1),999) -- Rick (MVP - Excel) "Rik_UK" wrote in message ... Rik I would suggest the following amendment as an enhanced version of the formula =LEFT(MID(A1,FIND("{",A1),LEN(A1)),FIND("}",MID(A1 ,FIND("{",A1),LEN(A1)))) this simplifies things slightly, and in your original post you said the number of characters between the brackets is not fixed... so this removes the limitation of 99 characters, as you have currently got. Hope this is ok... -- If this is the answer has helped please remember to click the yes button below... Kind regards Rik "Rick" wrote: On Dec 18, 5:52 am, Rick wrote: On Dec 17, 4:32 pm, "Rick Rothstein" wrote: Assuming the ID always comes at the end of the text (as you example shows)... =MID(A1,FIND("=",A1)+1,99) -- Rick (MVP - Excel) "Rick" wrote in message Rick: found one that works after poking around the archives for similar formulas,a dn utilizing your formula and inspiration. . . =LEFT(MID(A1,FIND("=",A1)+1,99),FIND("#",MID(A1,FI ND("=",A1)+1,99))-1) First bracket is always preceeded by a =, and last bracked is followed by a #. Many many thanks. Rick . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract portion of cell contents
On Dec 18, 12:26*pm, "L. Howard Kittle" wrote:
Hi Rick, Here's one more just for fun. =MID(A4,FIND("{",A4)+1,FIND("}",A4)-FIND("{",A4)-1) HTH Regards, Howard "Rick" wrote in message ... Have a text string that could look something like this: http://he4.clientthirddheytrs.com/wyt/cftts/edit.aspx?id={840102F6-AJUE-SD1B-9705-00188B2E03D9} I need to extract the ID number which is surrounded by "the brackets". Desired result in this instance would be: {840102F6-AJUE-SD1B-9705-00188B2} There is no consistency in the data; there can be any number of characters between the brackets, and any number of characters before, or after the brackets. *It's all formatted as text. Ideas? Thanks in advance. Rick- Hide quoted text - - Show quoted text - You guys are awesome! Thank you so much for your efforts on this. Rick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract portion of a cell | Excel Discussion (Misc queries) | |||
| How to use/extract only a portion of a field | New Users to Excel | |||
| Extract a specific portion of text as new cell value | Excel Discussion (Misc queries) | |||
| Extract portion of formula resident in a cell | Excel Worksheet Functions | |||
| I need to search for then extract a specific portion of cell data... | Excel Worksheet Functions | |||
![Rick[_10_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
