Thread: Extract data from String

I hope someone can help as this is driving me up the wall.

I have a text file that imports into Excel with data for each row in the
same cell. I need to split this data out into separate columns. The string is
a random length depending on the data in it. The example below shows the
string as it is, the following one shows how it should be split up into
separate columns.

"C W RUSSELL HAULAGE & PLA 5023 1 1 JCB 31-MAY-05 51 Hours 18.00 918.00"

C W RUSSELL HAULAGE & PLA
5023
1
1
JCB
31-MAY-05
51
Hours
18.00
918.00

The string will always be in the order above but will be different lengths
depending on the data within that row.

I have been trying to use the occurence of the first number to indicate the
end of the 1st part and go from there but have not had much success. There
will be a random number of spaces in the first part of the string depending
on the name of the supplier.

Any pointers would be greatly received.

Thank You

Regards

Sonya

If the data after the name is always in the same format then you
might be better starting from the right: something like (untested) -

Dim arrSegments, iLastSegment as integer
arrSegments = Split (strLine, " ")
iLastSegment = ubound(arrSegments) '

then:
arrSegments(iLastSegment) is 918.00
arrSegments(iLastSegment - 1) is 18.00
etc
and elements 0 to iLastSegment - 9 contain the supplier
name, which you can easily reassemble with a loop:
strSupplier = arrSegments(0)
for i = 1 to iLastSegment - 9
strSupplier = strSupplier & " " & arrSegments(i)
next

SL wrote:
> I hope someone can help as this is driving me up the wall.
>
> I have a text file that imports into Excel with data for each row in the
> same cell. I need to split this data out into separate columns. The string is
> a random length depending on the data in it. The example below shows the
> string as it is, the following one shows how it should be split up into
> separate columns.
>
> "C W RUSSELL HAULAGE & PLA 5023 1 1 JCB 31-MAY-05 51 Hours 18.00 918.00"
>
> C W RUSSELL HAULAGE & PLA
> 5023
> 1
> 1
> JCB
> 31-MAY-05
> 51
> Hours
> 18.00
> 918.00
>
> The string will always be in the order above but will be different lengths
> depending on the data within that row.
>
> I have been trying to use the occurence of the first number to indicate the
> end of the 1st part and go from there but have not had much success. There
> will be a random number of spaces in the first part of the string depending
> on the name of the supplier.
>
> Any pointers would be greatly received.
>
> Thank You
>
> Regards
>
> Sonya

Tom helped me with a massive string of differing lengths!!!

have a look at the post from yesterday, probably be of help.

Importing Long String - String Manipulation (EDI EANCOM 96a)



Brian


"SL" <SL@discussions.microsoft.com> wrote in message
news:3F490BCD-053E-4497-AC3E-56436D79D64B@microsoft.com...
>I hope someone can help as this is driving me up the wall.
>
> I have a text file that imports into Excel with data for each row in the
> same cell. I need to split this data out into separate columns. The string
> is
> a random length depending on the data in it. The example below shows the
> string as it is, the following one shows how it should be split up into
> separate columns.
>
> "C W RUSSELL HAULAGE & PLA 5023 1 1 JCB 31-MAY-05 51 Hours 18.00 918.00"
>
> C W RUSSELL HAULAGE & PLA
> 5023
> 1
> 1
> JCB
> 31-MAY-05
> 51
> Hours
> 18.00
> 918.00
>
> The string will always be in the order above but will be different lengths
> depending on the data within that row.
>
> I have been trying to use the occurence of the first number to indicate
> the
> end of the 1st part and go from there but have not had much success. There
> will be a random number of spaces in the first part of the string
> depending
> on the name of the supplier.
>
> Any pointers would be greatly received.
>
> Thank You
>
> Regards
>
> Sonya

Just use Text to columns with the space as the separator. You example seems
to imply that you will always have at least 10 fields separated by a space.
Always keep the last nine items separate.

If text to columns yields 10 items keep the ten items
if text to columns yields 11 items concatenate the first two
if text to columns yields 12 items concatenate the first three

Your example yields 15 items:
1 C
2 W
3 RUSSELL
4 HAULAGE
5 &
6 PLA
7 5023
8 1
9 1
10 JCB
11 31-May-05
12 51
13 Hours
14 18
15 918

so concatenate the first six.

--
Gary''s Student


"SL" wrote:

> I hope someone can help as this is driving me up the wall.
>
> I have a text file that imports into Excel with data for each row in the
> same cell. I need to split this data out into separate columns. The string is
> a random length depending on the data in it. The example below shows the
> string as it is, the following one shows how it should be split up into
> separate columns.
>
> "C W RUSSELL HAULAGE & PLA 5023 1 1 JCB 31-MAY-05 51 Hours 18.00 918.00"
>
> C W RUSSELL HAULAGE & PLA
> 5023
> 1
> 1
> JCB
> 31-MAY-05
> 51
> Hours
> 18.00
> 918.00
>
> The string will always be in the order above but will be different lengths
> depending on the data within that row.
>
> I have been trying to use the occurence of the first number to indicate the
> end of the 1st part and go from there but have not had much success. There
> will be a random number of spaces in the first part of the string depending
> on the name of the supplier.
>
> Any pointers would be greatly received.
>
> Thank You
>
> Regards
>
> Sonya

On Thu, 9 Feb 2006 06:41:31 -0800, "SL" <SL@discussions.microsoft.com> wrote:

>I hope someone can help as this is driving me up the wall.
>
>I have a text file that imports into Excel with data for each row in the
>same cell. I need to split this data out into separate columns. The string is
>a random length depending on the data in it. The example below shows the
>string as it is, the following one shows how it should be split up into
>separate columns.
>
>"C W RUSSELL HAULAGE & PLA 5023 1 1 JCB 31-MAY-05 51 Hours 18.00 918.00"
>
>C W RUSSELL HAULAGE & PLA
>5023
>1
>1
>JCB
>31-MAY-05
>51
>Hours
>18.00
>918.00
>
>The string will always be in the order above but will be different lengths
>depending on the data within that row.
>
>I have been trying to use the occurence of the first number to indicate the
>end of the 1st part and go from there but have not had much success. There
>will be a random number of spaces in the first part of the string depending
>on the name of the supplier.
>
>Any pointers would be greatly received.
>
>Thank You
>
>Regards
>
>Sonya

It would be fairly simple to implement a solution in either worksheet functions
or VBA depending on the precise nature of the data.

But for something like what you have:

Some assumptions:

1. String length <=255 characters
2. After the initial name, the remaining fields are separated by <space>'s; no
<space>'s are present which are not field separators; no empty fields.
3. No error checking is required for any of the fields

Worksheet solution: (there may be more elegant solutions as I'm new at regular
expressions, so if these don't work on your data please let me know).

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

2. Your string in A1

B1: =REGEX.MID($A$1,"((^|\s+)\S+)+(?=(\s+\S+){9})")
C1: =REGEX.MID($A$1,"\S+(?=(\s\S+){" & 9-COLUMNS($A:A)& "}$)")

Select B3 and copy/drag across to K1

The same functions (from morefunc.xll) can be run from within VBA using the RUN
method, or you can set a reference to the VBScript Regular Expressions and use
the functions in there (that would also be required if your string length was >
255. Some setup is required for that, so I'd just use the morefunc routines
even if I were using VBA.


--ron

Andrew

Thanks for the reply - I think this could work but could you explain exactly
how this works becasuse I may need to modify it slightly and don't quite
understand how arrays work.

Thanks


"Andrew Taylor" wrote:

> If the data after the name is always in the same format then you
> might be better starting from the right: something like (untested) -
>
> Dim arrSegments, iLastSegment as integer
> arrSegments = Split (strLine, " ")
> iLastSegment = ubound(arrSegments) '
>
> then:
> arrSegments(iLastSegment) is 918.00
> arrSegments(iLastSegment - 1) is 18.00
> etc
> and elements 0 to iLastSegment - 9 contain the supplier
> name, which you can easily reassemble with a loop:
> strSupplier = arrSegments(0)
> for i = 1 to iLastSegment - 9
> strSupplier = strSupplier & " " & arrSegments(i)
> next
>
> SL wrote:
> > I hope someone can help as this is driving me up the wall.
> >
> > I have a text file that imports into Excel with data for each row in the
> > same cell. I need to split this data out into separate columns. The string is
> > a random length depending on the data in it. The example below shows the
> > string as it is, the following one shows how it should be split up into
> > separate columns.
> >
> > "C W RUSSELL HAULAGE & PLA 5023 1 1 JCB 31-MAY-05 51 Hours 18.00 918.00"
> >
> > C W RUSSELL HAULAGE & PLA
> > 5023
> > 1
> > 1
> > JCB
> > 31-MAY-05
> > 51
> > Hours
> > 18.00
> > 918.00
> >
> > The string will always be in the order above but will be different lengths
> > depending on the data within that row.
> >
> > I have been trying to use the occurence of the first number to indicate the
> > end of the 1st part and go from there but have not had much success. There
> > will be a random number of spaces in the first part of the string depending
> > on the name of the supplier.
> >
> > Any pointers would be greatly received.
> >
> > Thank You
> >
> > Regards
> >
> > Sonya
>
>

SL wrote:
> I may need to modify it slightly and don't quite
> understand how arrays work.


Hi Sonya,

Here's another way that doesn't use an array:


Dim strInput As String
Dim iLen As Integer
Dim i As Integer, j As Integer, k As Integer

strInput = ActiveSheet.Range("A1").Text
iLen = Len(strInput)
i = 0
k = iLen
'ActiveSheet.Range("A2:A11").NumberFormat = "@" 'Formats as text.

Do
j = InStrRev(strInput, " ", k)
i = i + 1
ActiveSheet.Cells(12 - i, 1).Formula = Mid(strInput, j + 1, k - j)
k = j - 1
Loop Until i = 9

ActiveSheet.Cells(2, 1).Formula = Left(strInput, j - 1)


If you don't like number formats being changed, such as "18.00"
becoming "18", then uncomment the commented line. But if you want to do
math on those numbers, then leave it commented.


Good Luck,

Greg Lovern
http://PrecisionCalc.com
More Power In Excel

With a bit of tweaking I have managed to get there - thank you to all who
gave advice. Especially to Andrew and Greg.

Thank You

"Ron Rosenfeld" wrote:

> On Thu, 9 Feb 2006 06:41:31 -0800, "SL" <SL@discussions.microsoft.com> wrote:
>
> >I hope someone can help as this is driving me up the wall.
> >
> >I have a text file that imports into Excel with data for each row in the
> >same cell. I need to split this data out into separate columns. The string is
> >a random length depending on the data in it. The example below shows the
> >string as it is, the following one shows how it should be split up into
> >separate columns.
> >
> >"C W RUSSELL HAULAGE & PLA 5023 1 1 JCB 31-MAY-05 51 Hours 18.00 918.00"
> >
> >C W RUSSELL HAULAGE & PLA
> >5023
> >1
> >1
> >JCB
> >31-MAY-05
> >51
> >Hours
> >18.00
> >918.00
> >
> >The string will always be in the order above but will be different lengths
> >depending on the data within that row.
> >
> >I have been trying to use the occurence of the first number to indicate the
> >end of the 1st part and go from there but have not had much success. There
> >will be a random number of spaces in the first part of the string depending
> >on the name of the supplier.
> >
> >Any pointers would be greatly received.
> >
> >Thank You
> >
> >Regards
> >
> >Sonya
>
> It would be fairly simple to implement a solution in either worksheet functions
> or VBA depending on the precise nature of the data.
>
> But for something like what you have:
>
> Some assumptions:
>
> 1. String length <=255 characters
> 2. After the initial name, the remaining fields are separated by <space>'s; no
> <space>'s are present which are not field separators; no empty fields.
> 3. No error checking is required for any of the fields
>
> Worksheet solution: (there may be more elegant solutions as I'm new at regular
> expressions, so if these don't work on your data please let me know).
>
> 1. Download and install Longre's free morefunc.xll add-in from
> http://xcell05.free.fr
>
> 2. Your string in A1
>
> B1: =REGEX.MID($A$1,"((^|\s+)\S+)+(?=(\s+\S+){9})")
> C1: =REGEX.MID($A$1,"\S+(?=(\s\S+){" & 9-COLUMNS($A:A)& "}$)")
>
> Select B3 and copy/drag across to K1
>
> The same functions (from morefunc.xll) can be run from within VBA using the RUN
> method, or you can set a reference to the VBScript Regular Expressions and use
> the functions in there (that would also be required if your string length was >
> 255. Some setup is required for that, so I'd just use the morefunc routines
> even if I were using VBA.
>
>
> --ron
>