Convert String to cells

[Guest]

Hi

I have a line of data that looks like this

0000761H 001 01/06/2004 Paid Up 1,200.00

The thing is, it is all in one cell and I need to split it into 5 cells, separated by the spaces
Problem is I don't know where to start. Can anyone help?

 

[Bob Phillips]

Can you run Data>Text To Columns on it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Could do, but theres lots of lines and not a consistent number of data 'groups on each line. From what I know of that function, it would be a very manual process. I was after something in VBA to loop through and split out the data groups
I'm grateful for any pointers.

 

[Bob Phillips]

Is it one contiguous string, or is there a separator that the code or
whatever to get to?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Could do, but theres lots of lines and not a consistent number of data

'groups on each line. From what I know of that function, it would be a very
manual process. I was after something in VBA to loop through and split out
the data groups.

 

[Rob van Gelder]

Text to Columns to your answer - hopefully your rows have fixed column
widths.

By code:
assuming all columns are separated by at least 2 spaces:

Sub test()
Dim strTemp As String

strTemp = "0000761H " & _
"001 " & _
"01/06/2004 " & _
"Paid Up " & _
"1,200.00"

Range("A1:E1").Value = _
Split(Replace(Replace(Replace(Replace(Replace( _
strTemp, " ", Chr(1) & Chr(2)), Chr(2) & _
Chr(1), Chr(3)), Chr(2) & " ", Chr(3)), _
Chr(2), Chr(3)), Chr(3), ""), Chr(1))
End Sub

 

[Guest]

If the code was to loop down the rows in column A the problem may confounded in that there might be another string in say column C. I was thinking along these lines for the process: counting the data groups, inserting the same number of cells, populating the new cells with the 'split' data groups and then repeating the process for the next 6 columns
I can do most of it except count the data groups and spliting them.

 

[Guest]

The data groups all have two spaces+ between them and no spaces within, so in terms of identifying valid groups I could scan the string($) for two spaces followed by a character, and end the $ with a character followed by two spaces

Think I'm on track now, thanks for the mind jogging
Grateful for any sample code!

 

[Tom Ogilvy]

Data Text to columns can work on all the rows at once. It will not be
hindered by some rows having less groups than others. You would just need
to insert enough columns to the right to allow the split.

--
Regards,
Tom Ogilvy

The data groups all have two spaces+ between them and no spaces within, so

in terms of identifying valid groups I could scan the string($) for two
spaces followed by a character, and end the $ with a character followed by
two spaces.