Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing Question
Is there a formula or a macro that can take this string -
311[BCE]308[N] And parse out the data that is contained in the brackets [....] ? For example: 311[BCE]308[N] BCE N Where Col A is the string, and Col B and Col C are the results of the parsing. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing Question
Is there a formula or a macro that can take this string -
311[BCE]308[N] And parse out the data that is contained in the brackets [....] ? Are each of those numbers always going to be 3-digits long? If not, will one of them always 3-digits long (if so, which one)? Rick Rothstein (MVP - Excel) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing Question
On Apr 1, 1:04*pm, "Rick Rothstein"
wrote: Is there a formula or a macro that can take this string - 311[BCE]308[N] And parse out the data that is contained in the brackets [....] * ? Are each of those numbers always going to be 3-digits long? If not, will one of them always 3-digits long (if so, which one)? Rick Rothstein (MVP - Excel) Thanks for your help. The first bracketed data could be 1 to 5 characters. The second bracketed data will always by 1 character. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing Question
Thanks for your help. The first bracketed data could be 1 to 5
characters. The second bracketed data will always by 1 character. Use this formula for retrieving the contents of the first bracket... =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("]",A1)-1),"[",REPT(" ",99)),99)) and use this to get the contents of the last bracket... =MID(A1,LEN(A1)-1,1) Adjust the A1 references as needed... these formulas can be copied down or across as needed. Rick Rothstein (MVP - Excel) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing Question
Use this formula for retrieving the contents of the first bracket...
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("]",A1)-1),"[",REPT(" ",99)),99)) Actually, you can use this shorter and more efficient formula to do the same thing... =MID(LEFT(A1,FIND("]",A1)-1),FIND("[",A1)+1,5) Rick Rothstein (MVP - Excel) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing Question
On Apr 1, 1:57*pm, "Rick Rothstein"
wrote: Use this formula for retrieving the contents of the first bracket... =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("]",A1)-1),"[",REPT(" ",99)),99)) Actually, you can use this shorter and more efficient formula to do the same thing... =MID(LEFT(A1,FIND("]",A1)-1),FIND("[",A1)+1,5) Rick Rothstein (MVP - Excel) Thanks Again. I ran into a situation. In some cases, the 2nd set of bracketed data could be 2 characters in length. Can you help me modify your formula to cover this case ? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing Question
I ran into a situation. In some cases, the 2nd set of bracketed
data could be 2 characters in length. Can you help me modify your formula to cover this case ? We will need a totally different formula in that case (this is why I asked you do define the structure of your cell entries). Give this formula a try... =SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"[",REPT(" ",99)),99)),"]","") Rick Rothstein (MVP - Excel) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing Question
On Apr 6, 2:12*pm, "Rick Rothstein"
wrote: I ran into a situation. In some cases, the 2nd set of bracketed data could be 2 characters in length. Can you help me modify your formula to cover this case ? We will need a totally different formula in that case (this is why I asked you do define the structure of your cell entries). Give this formula a try... =SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"[",REPT(" ",99)),99)),"]","") Rick Rothstein (MVP - Excel) Than you. Worked great ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another Parsing address question | Excel Discussion (Misc queries) | |||
FOR RON ROSENFELD: PARSING QUESTION | Excel Discussion (Misc queries) | |||
Parsing | Excel Discussion (Misc queries) | |||
Data parsing question | Excel Worksheet Functions | |||
Parsing Data w/ a Formula (another question) | Excel Worksheet Functions |