splitting a text string into columns

[CROY1985]

Hi all

I need some help with splitting a text string as the text to columns function doesn't quite cut it with this particular set of text strings.

What we have is a list which contains

Reference, Name, Date, value, vat, total.

all seperated by spaces. The issues i have is that some of the names are single, some have 2 names, 3 names etc.

I want to split it so i can have a column for:

Reference
Name
Date
Value
Vat
Total

Here is an example of the text string:

1234567 John Smith 18/02/2013 100.00 20.00 120.00
1235678 Amy Jane Brown 17/01/2012 200.00 40.00 240.00
2345678 Thomas James Alex Watson 16/01/2012 1020.00 204.00 1224.00
3456798 Alan Jones 01/05/2012 2040.00 408.00 2448.00

The following are constant:
the first number is always 7 digits long,
the date is always in format dd/mm/yyyy

Other than that, the number of names varies, and the values vary. Just to be clear, for the above example i would want it to look like this:

1234567	John Smith	18/02/2013	100.00	20.00	120.00
1235678	Amy Jane Brown	17/01/2012	200.00	40.00	240.00
2345678	Thomas James Alex Watson	16/01/2012	1020.00	204.00	1224.00
3456798	Alan Jones	01/05/2012	2040.00	408.00	2448.00

<TBODY>
</TBODY>

Many Thanks

 

[s hal]

Have you tried delimiting by tab - perhaps the other variables are separated by tabs and not spaces. If everything is split by spaces, then that's another issue altogether. That said, I'd try the simplest solution first, which is making sure something other than spaces aren't separating SOME columns!

 

[CROY1985]

Nice one s hal - i didn't stop to try that.


Unfortunately, this has confirmed the string is seperated by spaces and not tabs.


I have made a start by using


=left(a1,7) to get the 7 digit number into a column. I am stuck after that, as everything else is variable length - apart from the date, but its position varies. **EDIT** where column A contains the text string.

 

[vds1]

IF A1 has

1234567 John Smith 18/02/2013 100.00 20.00 120.00

then

to get reference in B1 use

=LEFT(A1,FIND(" ",A1))

to get Name in c1 use

=TRIM(MID(A1,FIND(" ",A1),FIND("/",A1)-2-FIND(" ",A1)))

to get value, vat and total in D1 use

=TRIM(MID(A1,FIND("/",A1,FIND("/",A1)+1)+5,FIND(".",A1)))

Now in column D you can use text to column separated by space.

Hope this helps!

 

[L. Howard]

Try this for the date, but you still a ways to go for the rest.

=MID(A1,FIND("/",A1)-2,10)

Howard

 

[kevatarvind]

TRY BELOW

Excel Workbook
	A	B	C	D	E	F	G
1	1234567 John Smith 18/02/2013 100.00 20.00 120.00	1234567	John Smith	18/02/2013	100.00	20.00	120.00
2	1235678 Amy Jane Brown 17/01/2012 200.00 40.00 240.00	1235678	Amy Jane Brown	17/01/2012	200.00	40.00	240.00
3	2345678 Thomas James Alex Watson 16/01/2012 1020.00 204.00 1224.00	2345678	Thomas James Alex Watson	16/01/2012	1020.00	204.00	1224.00
4	3456798 Alan Jones 01/05/2012 2040.00 408.00 2448.00	3456798	Alan Jones	01/05/2012	2040.00	408.00	2448.00
Sheet1

 

[Peter_SSs]

Assuming ..
- dates are all dd/mm/yyyy
- last 3 numbers in each row have 2 decimal places
- first numbers are all 7 digits

Try these copied down.

If you dont require the numbers to be actual numbers or dates to be actual dates, you could omit the "+0" from the end of the relevant formulas.
You may need to format the date and number columns appropriately after entering the formulas.

Excel Workbook
	A	B	C	D	E	F	G
1	Data	Reference	Name	Date	Value	Vat	Total
2	1234567 John Smith 18/02/2013 100.00 20.00 120.00	1234567	John Smith	18/02/2013	100.00	20.00	120.00
3	1235678 Amy Jane Brown 17/01/2012 200.00 40.00 240.00	1235678	Amy Jane Brown	17/01/2012	200.00	40.00	240.00
4	2345678 Thomas James Alex Watson 16/01/2012 1020.00 204.00 1224.00	2345678	Thomas James Alex Watson	16/01/2012	1020.00	204.00	1224.00
5	3456798 Alan Jones 01/05/2012 2040.00 408.00 2448.00	3456798	Alan Jones	01/05/2012	2040.00	408.00	2448.00
Extract

@kevatarvind
When using Excel jeanie, please use the ‘Analyse range (Forum)’ field near the top left to restrict the number of formulas generated. There is generally no need to display multiple formulas that are basically the same, it just fills up the board and makes your post and the thread hard to read/navigate.

 

[Peter_SSs]

If Vat is always 20% of Value then my F2 formula could be replaced by

=E2*0.2