CODEWITHSUNDEEP
vbaexceltextsplit
Nur Atiqah Hassan
Nur Atiqah Hassan

Reputation: 167

How do i split text to a different column or remove a text from the original column?

I've copied a column of data starting from D9 to D-whatever, the copied data have both decimal value and text. The data varies in each cell in column D

Example

D9 : 1675.87 L/s D10 : 1555.87 L/s D11 : 1635.87 L/s This is my code i tried.. 

    Dim c As Collection, K As Long
     Set c = New Collection
     K = 9
     On Error Resume Next
     For Each r In Range("D9:D" & Cells(Rows.Count, "D").End(xlUp).Row)
     ary = Split(r.Text, ",")
     For Each a In ary
     c.Add a, CStr(a)
     If Err.Number = 0 Then
     Cells(K, "E").Value = a
     K = K + 1
     Else
     Err.Number = 0
     End If
     Next a
     Next r
     On Error GoTo 0

I want to split the data so that it will be D6 1675.87 and E6 L/s OR remove the L/s completely.

I know this is simple for most people but I'm relatively new at this so any help would be good. Thank you. You are much appreciated.

Upvotes: 1

Views: 242

Answers (4)

Byron Wall
Byron Wall

Reputation: 4010

As noted, Split is the easy way to do this. If you know that you will always have a single space you can get all of the cells very quickly with

Sub SplitAndRewrite()

    Dim rng_start As Range
    Set rng_start = Range("D6")

    Dim rng_cell As Range
    For Each rng_cell In Range(rng_start, rng_start.End(xlDown))
        rng_cell.Resize(, 2) = Split(rng_cell, " ")
    Next
End Sub

Code works by iterating through a contiguous (uses End) column of values and applying Split on the . It then takes the two values and pops them back on top of the cell using Resize to expand the output by one column.

Split returns an array so it can be quickly output back into the spreadsheet.

Upvotes: 1

R3uK
R3uK

Reputation: 14537

So test that :

Sub SplitValues()
Dim aSplit As Variant

With ActiveSheet
    For I = 2 To Cells(.Rows.Count, "D").End(xlUp).Row
        aSplit = Split(Cells(I, "D"), " ", 2)

        'Write in D and E columns (erase data already in D)
        Cells(I, "D") = aSplit(0)
        Cells(I, "E") = aSplit(1)

        'Write in E and F columns (don't erase data already in D)
        'Cells(I, "E") = ArrSplit(0)
        'Cells(I, "F") = ArrSplit(1)
    Next I
End With

End Sub

You may simply use a built-in Excel tool :

In Data tab, Convert tool :

  1. Choose Delimited
  2. Chose space as Separator

and just enjoy

No need for VBA there, if you want just take a look at the split function there

Upvotes: 2

Dawid
Dawid

Reputation: 786

Read about Split function -> https://msdn.microsoft.com/en-us/library/6x627e5f%28v=vs.90%29.aspx

Sub LittleExample()

Dim ArrSplit As Variant

Cells(6, "D") = "1675.87 L/s"

ArrSplit = Split(Cells(6, "D"), " ")

Cells(6, "D") = ArrSplit(0) ' 1675.87
Cells(6, "E") = ArrSplit(1) ' L/s

End Sub

Upvotes: 1

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

If you go to the Data tab on the Ribbon you will see a Text to Columns tool.

Select the data you want to split then click on Text to Columns.

In Step 1, select the Delimited option and click Next.

In Step 2, select just the Space delimiter and click Next.

In Step 3 you will see a preview of how your data will be split and have the option to select a column and not import it.

Upvotes: 1

Related Questions