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

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

On Wed, 23 Jan 2008 22:14:10 -0800, 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

Are your data entries all on one line within the cell? As in the above two
lines:

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

If so, the following Macro should do what you want. As written, it operates on
"Selection" and writes the split results into the four adjacent columns on the
same row.

If you want to replace the original data, see the instructions in the macro
comments in two areas.

================================================
Option Explicit

Sub SplitCodes()
Dim c As Range
Dim i As Long
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(\d{1,4})\s+(.*?)\s+([0-9,.]+)\s+([0-9,.]+)$"
For Each c In Selection
'clear adjacent cells
'if replacing source column, change "c(1,5)" to "c(1,4)"
Range(c(1, 2), c(1, 5)).Clear
If re.test(Trim(c.Text)) = True Then
Set mc = re.Execute(Trim(c.Text))
For i = 0 To 3
'In line below, change "i+1" to "i" to replace source column
c.Offset(0, i + 1).Value = mc(0).submatches(i)
Next i
End If
Next c
End Sub
==============================================

To enter the macro <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code above into the window that opens.

To use it, select the range you wish to process. <alt<F8 opens the macro
dialog box. Select the SplitCodes macro and RUN.
--ron

On Thu, 24 Jan 2008 09:38:57 -0500, Ron Rosenfeld
wrote:

On Wed, 23 Jan 2008 22:14:10 -0800, 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

Are your data entries all on one line within the cell? As in the above two
lines:

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

If so, the following Macro should do what you want. As written, it operates on
"Selection" and writes the split results into the four adjacent columns on the
same row.

If you want to replace the original data, see the instructions in the macro
comments in two areas.

=============================================== =
Option Explicit

Sub SplitCodes()
Dim c As Range
Dim i As Long
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(\d{1,4})\s+(.*?)\s+([0-9,.]+)\s+([0-9,.]+)$"
For Each c In Selection
'clear adjacent cells
'if replacing source column, change "c(1,5)" to "c(1,4)"
Range(c(1, 2), c(1, 5)).Clear
If re.test(Trim(c.Text)) = True Then
Set mc = re.Execute(Trim(c.Text))
For i = 0 To 3
'In line below, change "i+1" to "i" to replace source column
c.Offset(0, i + 1).Value = mc(0).submatches(i)
Next i
End If
Next c
End Sub
==============================================

To enter the macro <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code above into the window that opens.

To use it, select the range you wish to process. <alt<F8 opens the macro
dialog box. Select the SplitCodes macro and RUN.
--ron


Here's another version that does not use vbscript regular expressions:

---------------------------
Option Explicit

Sub SplitData()
Dim c As Range
Dim sTemp() As String
Dim sT As String
Dim i As Long
For Each c In Selection
Range(c(1, 2), c(1, 5)).Clear
sTemp = Split(Application.WorksheetFunction.Trim(c.Text))
If UBound(sTemp) = 3 Then
c.Offset(0, 1).Value = sTemp(0)
i = 1
sT = ""
Do Until i = UBound(sTemp) - 1
sT = sT & sTemp(i) & " "
i = i + 1
Loop
c.Offset(0, 2).Value = Trim(sT)
c.Offset(0, 3).Value = sTemp(UBound(sTemp) - 1)
c.Offset(0, 4).Value = sTemp(UBound(sTemp))
End If
Next c
End Sub
------------------------------------------------
--ron