#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 42
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 42
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 42
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 42
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Another Parsing address question HSL Excel Discussion (Misc queries) 8 July 8th 08 11:31 PM
FOR RON ROSENFELD: PARSING QUESTION ORLANDO V[_2_] Excel Discussion (Misc queries) 4 April 15th 08 11:08 PM
Parsing Saxman[_2_] Excel Discussion (Misc queries) 3 July 30th 07 04:36 PM
Data parsing question Dan Neely Excel Worksheet Functions 0 July 19th 05 12:40 AM
Parsing Data w/ a Formula (another question) carl Excel Worksheet Functions 2 December 3rd 04 07:51 PM


All times are GMT +1. The time now is 03:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"