Exel Macro Help

[mrhonnesty99]

i need help with this issue if any one can help, i think its more of a challenge since i asked a few people to help and no one could.

i need a macro to help me sort information into cells,

#1) For example in the picture you see A1 has the product name and i want it to be pasted in G2. the cell A1 will always have the item name

#2) the second cell is the Product code in this case it is 5H2546YDR that was sent to sell H2

#3) Category 430 was sent to cell i2

#4) Retail price sent to cell k2

#5) Price we pay sent to j2

#6) if you notice i would like to use all the ********************************** to separate the product or have the macro recognize this as a command so that it can start the search again looking for the same information and Product Name, Product Code,Category,Price We Pay and Retail Price.



you get the idea i mind doing this because it gets boring after the first 400 items

please note the empty cells in between **************** to the next cell with information that has the item name will vary some times 1 empty row of cells up to 8 empty rows.



[url]http://i184.photobucket.com/albums/x223/Gmoney2004_photos/Macro%20Help/MACROHELP.jpg[/URL]

 

[Michael M]

Hi and Welcome to the Board.
Creating the table isn't really the problem.
The major issue here is, the way the data is presented. Is it imported from somewhere, typed in manually, copied/ pasted ??

If there is a way to "clean up" the initial data, it should be OK.
This reply has also bumped you back to the top of the list.

 

[mrhonnesty99]

i can re clean the data and have it as such


Product Nameÿ5-HTP 100 mg 180 vcaps
Product Code¢ 5HYD9
Manufacturer: Pure Encapsulations (PUR)
Vendor Item Code: HYD11
Category£430
No Rating (0 reviews)
Product Nameÿ
Product Nameÿ
Retail Price: $114.50
Price We Pay¥ $57.25
Margin: $57.25 (50%)
********************************************************************


Product Nameÿ
}
Product Nameÿ5-HTP 100 mg 60 vcaps
Product Code¢ 5HYD8
Manufacturer: Pure Encapsulations (PUR)
Vendor Item Code: HYD16
Category£430
No Rating (0 reviews)
Product Nameÿ
Product Nameÿ
Retail Price: $44.60
Price We Pay¥ $22.30
Margin: $22.30 (50%)
********************************************************************



=TRIM(MID(A1,SEARCH("ÿ",A1)+1,255))
=TRIM(MID(A1,SEARCH("¢",A1)+1,255))
=TRIM(MID(A1,SEARCH("£",A1)+1,255))
=TRIM(MID(A1,SEARCH("¥",A1)+1,255))

how can i make the codes above to skip if no information is available after the special character.

 

[Michael M]

=if(a1="","",trim(mid(a1,search("ÿ",a1)+1,255)))

IS that what you mean ??