Split String into Cells with incased values []

**ezykiwi** · 07-22-2008, 04:11 AM

Howdy All

Ive got a tricky problem that hopefully someone has the answer too....
I am currently getting data in the following format...

[ ] [ 1.00] [001] [Item 1] [1 ] [ 0]
[ ] [ 2.00] [002] [Item 2] [2 ] [ 0]
[ ] [ 3.00] [003] [Item 3] [3 ] [ 0]
[ ] [ 4.00] [004] [Item 4] [4 ] [ 1000]
[ ] [ 5.00] [005] [Item 2] [5 ] [ 0]

Im currently having to rewrite it when i get it which is crazy time consuming..
If i paste it into excel it goes into 5 cells.. A1 : A5 ... one on each line which is cool.... so id like to be able to extract all the data out of between the [ ] 's

B1 would = Blank (since there is no data in it...)
C1 would = 1.00
D1 would = 001

Does anyone know of a way of doing this.. ive spent time looking through google etc but im struggling to come up with a solution...

Thanks in advance...
Ezy

**corinereyes** · 07-22-2008, 05:08 AM

Hi,

May by Using the MID function. Like for example:

Data starts in column A1 in

B1: =MID($A1,6,5)
C1: =MID($A1,14,3)
D1: =MID($A1,29,1)
E1: =MID($A1,35,1)

and drag to copy the formula down.

This will work if the format of data are the same: Example attached.

**ezykiwi** · 07-22-2008, 05:23 AM

The values between the []'s (brackets) will vary sometimes.....

Im wondering if i deleted all the opening [ and delimited based on the ]
that might work.... then the values between them would not need to be consistant...

Cheers for the advice though.... thats a good thought.. but i just dont think it will work in this case...

Cheers Ezy