Extracting certain items from each cell
 

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

--
johncassel
-----------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...fo&userid=2501
View this thread: http://www.excelforum.com/showthread.php?threadid=56103

Extracting certain items from each cell
 

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"
wrote in message
...

Hello,

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

I would usually use Text to columns for this but the data is not
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 number
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

John


--
johncassell
------------------------------------------------------------------------
johncassell's Profile:
http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=561036

Extracting certain items from each cell
 

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

NickHK

"NickHK" wrote in message
...
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"
wrote in message
...

Hello,

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

I would usually use Text to columns for this but the data is not
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 number
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

John


--
johncassell
------------------------------------------------------------------------
johncassell's Profile:
http://www.excelforum.com/member.php...o&userid=25016
View this thread:
http://www.excelforum.com/showthread...hreadid=561036

Extracting certain items from each cell
 

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"
wrote in message
...

Hello,

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

I would usually use Text to columns for this but the data is not
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 number
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

John


--
johncassell
------------------------------------------------------------------------
johncassell's Profile:
http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=561036