Extract texts & numbers from one cell into four cells

[Ed]

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

 

[carlo]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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