Parsing Question

[carl]

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.

 

[Rick Rothstein]

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)

 

[carl]

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.

 

[Rick Rothstein]

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)

 

[Rick Rothstein]

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)

 

[carl]

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 ?

 

[Rick Rothstein]

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)

 

[carl]

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 !