D
dmva
I am pasting a web page (such fun!) to Excel, using Paste Special an
the Text option.
It gives me the information pertaining to each item that I want sprea
into two rows, which slighly complicates things to begin with. Ther
is also information in each row that is not easily separable by usin
the Data>Text to columns, without using that tool over and over an
over and over and over.
I'd like to know if anyone can help with a formula/formulas to tak
this text, separate it out into columns all on the same row. Or mayb
a macro. I am not concerned with appearance, as all the informatio
that is separated into the various columns will be linked to anothe
worksheet for appearance. So, I don't care if everything is jus
shifted to the right/left of the original column and it remains.
Here are three examples of the data I have:
Item #1
Row 1: paderico1(4) $24.99 01035-9511 May-20
Row 2: 1939-54 new idler arm 264953 (7082179737)
Item #2
Row 3: fried_iguana(82) $5.99 951294243 May-20
Row 4: 1967 15" frt fender extensions LH/RH 3901798-9 (7082179764)
Item #3
Row 5: 2thdoc87(44) $142.86 CH4001 May-20
Row 6: 1960's Edelbrock manifolds poster (7082178345)
First line can have the following variations. First item is a cod
name, and can have two words or more, but they are either run togethe
with no spaces, or have the underscore. They can end in a number (a
in pabenco1 above) or begin with a number (as 2thdoc87 above), or hav
no number. They all then have a 1 to 4 digit number (rating for th
code name) in parentheses afterwards. The number in parentheses is on
thing I need separated from the code word, as I am not going to use it
but putting it in a separate column will let me ignore it for now, an
be able to use it later if I decide.
Next item in first line is a price - pretty straightforward, probabl
none over $999.99.
Next item is a zip code. First example you see is a zip plus fou
separated by a dash. Some of these are just 5 digit zips, some ar
plus fours, some are Canadian (letter-digit-letter, space
digit-leter-digit), some foreign (usually two letters, then 4 digits).
The one from the second example above is acually a zip plus fou
without the dash in it, as it was entered incorrectly in the origina
webpage data. I have seen a couple of things from some posters here i
this forum that deal with straigtening out zip codes, and assume I ca
deal with that in the column formatting, but just wanted to show th
variations encountered.
Last item in first row is a date - again, pretty straightforward.
Second row, first item is the title of the item, and it can vary quit
a bit. Most (but not all) of them start out with a number (year), bu
can be single year, or range, as seen in the above examples. The titl
wording can have multiples of words, and can include numbers in th
actual title itself, beginning, middle or end (like the 15" in the on
example), in addition to the other number which you can see there, tha
is the part number (263953 and 3901798-9 in the examples above). Par
numbers are not in all of the item titles (as in Edelbrock poste
example), and need to be left in with the title, not separated int
separate columns.
Second row, last item is a sale invoice number. I would like to hav
it in a separate column without the parentheses. I'd also like to hav
it with spaces between some of the digits, i.e.
708 217 9764
Here is what I would like to have these examples look like in eac
column. Each line down below represents a single column.
Example 1 becomes Row 1:
Col A: paderico1
Col B: (4)
Col C: $24.99
Col D: 01035-9511
Col E: May-20
Col F: 1939-54 new idler arm 264953
Col G: 708 217 9737
Example 2 becomes Row 2:
Col A: bbqd_iguana
Col B: (82)
Col C: $5.99
Col D: 95129-4243
Col E: May-20
Col F: 1967 15" frt fender extensions LH/RH 3901798-9
Col G: 708 217 9764
Example 3 becomes Row 3:
Col A: 2thdoc87
Col B: (44)
Col C: $142.86
Col D: CH4001
Col E: May-20
Col F: 1960's Edelbrock manifolds poster
Col G: 708 217 8345
Original Data will be pasted into Column I (using Col H as a spacer).
May be a lot to ask? I appreciate any help.
Thanks much.
Dan
the Text option.
It gives me the information pertaining to each item that I want sprea
into two rows, which slighly complicates things to begin with. Ther
is also information in each row that is not easily separable by usin
the Data>Text to columns, without using that tool over and over an
over and over and over.
I'd like to know if anyone can help with a formula/formulas to tak
this text, separate it out into columns all on the same row. Or mayb
a macro. I am not concerned with appearance, as all the informatio
that is separated into the various columns will be linked to anothe
worksheet for appearance. So, I don't care if everything is jus
shifted to the right/left of the original column and it remains.
Here are three examples of the data I have:
Item #1
Row 1: paderico1(4) $24.99 01035-9511 May-20
Row 2: 1939-54 new idler arm 264953 (7082179737)
Item #2
Row 3: fried_iguana(82) $5.99 951294243 May-20
Row 4: 1967 15" frt fender extensions LH/RH 3901798-9 (7082179764)
Item #3
Row 5: 2thdoc87(44) $142.86 CH4001 May-20
Row 6: 1960's Edelbrock manifolds poster (7082178345)
First line can have the following variations. First item is a cod
name, and can have two words or more, but they are either run togethe
with no spaces, or have the underscore. They can end in a number (a
in pabenco1 above) or begin with a number (as 2thdoc87 above), or hav
no number. They all then have a 1 to 4 digit number (rating for th
code name) in parentheses afterwards. The number in parentheses is on
thing I need separated from the code word, as I am not going to use it
but putting it in a separate column will let me ignore it for now, an
be able to use it later if I decide.
Next item in first line is a price - pretty straightforward, probabl
none over $999.99.
Next item is a zip code. First example you see is a zip plus fou
separated by a dash. Some of these are just 5 digit zips, some ar
plus fours, some are Canadian (letter-digit-letter, space
digit-leter-digit), some foreign (usually two letters, then 4 digits).
The one from the second example above is acually a zip plus fou
without the dash in it, as it was entered incorrectly in the origina
webpage data. I have seen a couple of things from some posters here i
this forum that deal with straigtening out zip codes, and assume I ca
deal with that in the column formatting, but just wanted to show th
variations encountered.
Last item in first row is a date - again, pretty straightforward.
Second row, first item is the title of the item, and it can vary quit
a bit. Most (but not all) of them start out with a number (year), bu
can be single year, or range, as seen in the above examples. The titl
wording can have multiples of words, and can include numbers in th
actual title itself, beginning, middle or end (like the 15" in the on
example), in addition to the other number which you can see there, tha
is the part number (263953 and 3901798-9 in the examples above). Par
numbers are not in all of the item titles (as in Edelbrock poste
example), and need to be left in with the title, not separated int
separate columns.
Second row, last item is a sale invoice number. I would like to hav
it in a separate column without the parentheses. I'd also like to hav
it with spaces between some of the digits, i.e.
708 217 9764
Here is what I would like to have these examples look like in eac
column. Each line down below represents a single column.
Example 1 becomes Row 1:
Col A: paderico1
Col B: (4)
Col C: $24.99
Col D: 01035-9511
Col E: May-20
Col F: 1939-54 new idler arm 264953
Col G: 708 217 9737
Example 2 becomes Row 2:
Col A: bbqd_iguana
Col B: (82)
Col C: $5.99
Col D: 95129-4243
Col E: May-20
Col F: 1967 15" frt fender extensions LH/RH 3901798-9
Col G: 708 217 9764
Example 3 becomes Row 3:
Col A: 2thdoc87
Col B: (44)
Col C: $142.86
Col D: CH4001
Col E: May-20
Col F: 1960's Edelbrock manifolds poster
Col G: 708 217 8345
Original Data will be pasted into Column I (using Col H as a spacer).
May be a lot to ask? I appreciate any help.
Thanks much.
Dan