Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting part of a cell | Excel Worksheet Functions | |||
Extracting from a cell | Excel Discussion (Misc queries) | |||
subtracting (extracting non-duplicate items)HELP!! | Excel Worksheet Functions | |||
Extracting items from Autofilter drop down | Excel Programming | |||
extracting unique items | Excel Programming |