Excel Column C:C Has Numerals and Text I need Numerals in Column B:B and Text in Column C:C

Question

Hello from Steve

Excel Column C:C Has Numerals and Text I need Numerals in Column B:B and Text in Column C:C

For example X8225 FULL CREAM is in Column C:C

I need please X8225 to be put in Column B:B and FULL CREAM to stay in Column C:C

For example X4537 MAROKOPA FALLS is in Column C:C

I need please X4537 to be put in Column B:B and MAROKOPA FALLS to stay in Column C:C

Can I have "VBA" to do this please

Answer 1

Hi,

If you want a non-VBA solution, you may use the TEXTBEFORE() and TEXTAFTER() functions.

Answer 2

Hello Barry

firstly thank you

How do you know which line it stops at?

The below is highlighted in Yellow

numPart = Trim(Left(cellValue, InStr(1, cellValue, " ") - 1)) ' Get the numeral part"

What do you mean by it stops? Is there an error message? "No"

If it only gets to that line and quits, how does it remove values from column B "I have no idea"

The Code is AI generated.

If you can upload a sample spreadsheet to a public site we could provide much better support.

I do not know how to upload my spreadsheet.

Answer 3

Hello From Steve

It is now deleting numerals in Column B:B

It is also only getting to the below before it continues to the end.

numPart = Trim(Left(cellValue, InStr(1, cellValue, " ") - 1)) ' Get the numeral part

Answer 4

Hello from Steve

It has an error

numPart = Trim(Left(cellValue, InStr(1, cellValue, " ") - 1)) ' Get the numeral part

Answer 5

To achieve the task of separating numerals and text from Column C into Columns B and C in Excel using VBA, you can use the following code:

Sub SeparateNumeralsAndText()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cellValue As String
    Dim numPart As String
    Dim textPart As String

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

    ' Find the last row in Column C
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    ' Loop through each cell in Column C
    For i = 1 To lastRow
        cellValue = ws.Cells(i, "C").Value
        ' Split the cell value into numerals and text
        numPart = Trim(Left(cellValue, InStr(1, cellValue, " ") - 1)) ' Get the numeral part
        textPart = Trim(Mid(cellValue, InStr(1, cellValue, " ") + 1)) ' Get the text part

        ' Place the parts in Columns B and C
        ws.Cells(i, "B").Value = numPart
        ws.Cells(i, "C").Value = textPart
    Next i
End Sub

Instructions to Use the Code:

1. Open Excel and press ALT + F11 to open the VBA editor.
2. Insert a new module by right-clicking on any of the items in the Project Explorer and selecting Insert > Module.
3. Copy and paste the above code into the module window.
4. Change the sheet name in Set ws = ThisWorkbook.Sheets("Sheet1") to match your actual sheet name.
5. Close the VBA editor and run the macro by pressing ALT + F8, selecting SeparateNumeralsAndText, and clicking Run.

This code will loop through each cell in Column C, separate the numerals and text, and place them in Columns B and C respectively.