Hi Ed

you can do following:
A1 is your weird cell (source)
B1: =LEFT(A1,FIND(" ",A1)-1)
C1: =MID(TRIM(A1),LEN(B1)+2,LEN(TRIM(A1))-LEN(B1)-LEN(D1)-LEN(E1)-3)
D1: =RIGHT(LEFT(TRIM(A1),LEN(TRIM(A1))-
LEN(E1)-1),LEN(LEFT(TRIM(A1),LEN(TRIM(A1))-LEN(E1)-1))-
LOOKUP(33000,FIND(" ",LEFT(TRIM(A1),LEN(TRIM(A1))-
LEN(E1)-1),ROW(A:A))))
E1: =RIGHT(TRIM(A1),LEN(TRIM(A1))-LOOKUP(33000,FIND("
",TRIM(A1),ROW(A:A))))

hth
Carlo

On Jan 24, 3:14 pm, Ed wrote:
Hi there,

My query is on how to separate texts and numbers (including "Item Code",
"Product Names", "Stock Sold" and "Stock Remain") from one cell into four
cells on the same worksheet.

1. The "Item Code" could have One to Four digits.
2. The "Product Name" is in various lengths, could have four words with
spacing in between.
3. "Stock Sold" and "Stock Remain" both could have a range between 0 and
9,999,999,999.99 (there will be no negative value)
4. The spacing between these four pieces of information could be varies as
well.

Example One:
Item Code: 1
Product Name: AAA
Stock Sold: 1
Stock Remain: 9,999,999,999,999.99

1 AAA 1.00 9,999,999,999,999.99

Example Two:
Item Code: 9999
Product Name: A B C D
Stock Sold: 9,999,999,999,999.99
Stock Remain: 1,000,000,000.00

9999 A B C D 9,999,999,999,999.99 1,000,000,000.00

Many thanks and really appreciate for your assistance!

Ed