Extracting certain items from each cell

[johncassell]

Hello,

Could someone please help with this problem:-
I have copied some columns of data from PDF and pasted into Excel whic
has dumped it all on one column.

I would usually use Text to columns for this but the data is no
logical as usual.

The columns look like this:

104B DOW REWASH 25.0 25.0
105 HOT WATER SPIN 53.8 50.0
1054 GREEN STRIP ON: OFF: 0.0 0.0
105E HOT WATER WASH(EMPICOL) 155.0 155.0
105SOAP 105 HOT WASH (SOAP) 0.0 0.0
105Z HOT WATER SPIN 0.0 0.0

Now the first bit of text is the code (104B, 105SOAP etc.)
The second bit is the description (DOW REWASH, 105 HOT WASH(SOAP
etc..)
The first number after the description is the list price and the numbe
after that is the customer price.

Can anyone think of a way I can quickly get this onto 4 columns??

Any help would be greatly appreciated.

Thanks

Joh

 

[NickHK]

John,
Something like this. No to add error trapping:
Function GetParts(argDataIn As String) As Variant
Dim Temp As Variant
Dim Description As String
Dim i As Long
Dim Result(3) As Variant
Temp = Split(argDataIn, " ")
For i = 1 To UBound(Temp) - 2
Description = Description & Temp(i) & " "
Next
Description = Left(Description, Len(Description) - 1)
Result(0) = Temp(0)
Result(1) = Description
Result(2) = Temp(UBound(Temp) - 1)
Result(3) = Temp(UBound(Temp))
GetParts = Result
End Function

NickHK

"johncassell" <[email protected]>
wrote in message
news:[email protected]...

 

[NickHK]

That should say "Need to add error trapping:"
Assumes you are using Option Base 0.

NickHK

John,
Something like this. No to add error trapping:
Function GetParts(argDataIn As String) As Variant
Dim Temp As Variant
Dim Description As String
Dim i As Long
Dim Result(3) As Variant
Temp = Split(argDataIn, " ")
For i = 1 To UBound(Temp) - 2
Description = Description & Temp(i) & " "
Next
Description = Left(Description, Len(Description) - 1)
Result(0) = Temp(0)
Result(1) = Description
Result(2) = Temp(UBound(Temp) - 1)
Result(3) = Temp(UBound(Temp))
GetParts = Result
End Function

NickHK

"johncassell" <[email protected]>
wrote in message

 

[Bob Phillips]

B1: =LEFT(A1,FIND(" ",A1)-1)
C1: =SUBSTITUTE(SUBSTITUTE(A1," "&E1,""),B1&" ","")
D1: =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,B1&"
","")&"0123456789"))+LEN(B1),
FIND(" ",A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,B1&"
","")&"0123456789")))-
MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,B1&" ","")&"0123456789"))+1)
E1:=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"johncassell" <[email protected]>
wrote in message
news:[email protected]...