Split not working excel VBA

Question

Input in column H from Row 2

column H
90 million +
550 million +
150 million +
2.1 billion +
110 million +
.
.
.

Expected Output in Column J from Row 2

VBA Code:

Option Explicit
Sub ConvertRange()
Dim str As String
Dim Cet
Dim i, j, l As Long
Dim FinalRow As Long
Dim wk As Worksheet
Application.ScreenUpdating = False


Set wk = Sheets("Sheet1")

FinalRow = wk.Range("H900000").End(xlUp).Row

For i = 2 To FinalRow
str = Cells(i, "H").Value
str = Replace(str, " ", "+")
str = Replace(str, "", "+")
str = Replace(str, " ", "+")
Cet = Split(str, "+")

Range("J" & i) = Cet(LBound(Cet))


Next i
Application.ScreenUpdating = True
End Sub

But I am getting wrong Output in Column J As follows

90 million 
550 million 
150 million 
2.1 billion 
110 million 
.
.
.

I believe either of these commands would have replaced the white spaces with the + sign

str = Replace(str, "", "+")
str = Replace(str, " ", "+")

And following line would have an Array wherein LBound value id Cet should be only numbers

Cet = Split(str, "+")

Where am I wrong ?

Basically what I want is Cet(0) should be the long or integer number and Cet(1) should be the text billion or million depending on the text in col H for every loop so that I can process this further

So if Column H Row 2 value is 90 million Cet(0)=90 and Cet(1)=million

Just split on the space and keep the fist one:Range("J" & i) = split(str, " ")(0). Without all the Replace — Scott Craner
– Scott Craner, Commented Nov 12, 2015 at 20:06
str = Replace(str, "", "+") is not replacing white space. It's replacing nothing. In other words, a replacement isn't happening at all where you think it is. I would add that you probably don't need VBA for this; a simple formula would suffice. — Marc
– Marc, Commented Nov 12, 2015 at 20:06
I don't know your intentions so this may be moot but removing units may be a bad idea given that 2.1 is the largest number here... — findwindow
– findwindow, Commented Nov 12, 2015 at 20:14
So after split via space, check first char of the second element for "b" and multiply by 1000? — findwindow
– findwindow, Commented Nov 12, 2015 at 20:29

Ron Rosenfeld · Accepted Answer · 2015-11-12 20:45:21Z

4

It seems one issue may be that there is not a space between the integer and the text string. Rather there is a NBSP. That being the case, the following should work:

Option Explicit
Sub ConvertRange()
    Dim Cet
    Dim FinalRow As Long
    Dim wk As Worksheet
    Dim i As Long

Set wk = Sheets("Sheet1")
With wk
    FinalRow = .Cells(.Rows.Count, "H").End(xlUp).Row

    For i = 2 To FinalRow
        Cet = Split(Replace(.Cells(i, "H"), Chr(160), Space(1)))
        .Range("J" & i) = Cet(0)
        .Range("K" & i) = Cet(1)
    Next i
End With


End Sub

answered Nov 12, 2015 at 20:45

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Stupid_Intern Over a year ago

Yes it worked thank you may I knw what does NBSP stands for

Ron Rosenfeld Over a year ago

@RohanK NBSP = Non breaking space; or no break space. Check it in Wikipedia or Google for a comprehensive answer.

user4039065 Over a year ago

nice work identifying the non-breaking space from the sample data.

Ron Rosenfeld Over a year ago

@Jeeped Thanks. Fortunately, the OP must have copied and pasted the data. Probably the original came from an HTML document or web page.

SierraOscar · Accepted Answer · 2015-11-12 20:14:21Z

1

Split() is working fine, you're just not supplying the correct arguments for your Replace() method.

I believe either of these commands would have replaced the white spaces with the + sign
str = Replace(str, "", "+")
str = Replace(str, "", "+")

"" is just an empty string - you want to replace new line characters for which there are vbConstants:

vbCr      '// Carriage Return
vbLf      '// Line Feed
vbCrLf    '// Carriage Return Line Feed
vbNewLine '// Carriage Return Line Feed

Or you can use the Chr() code:

Chr(13)    '// Carriage Return
Chr(10)    '// Line Feed

So you need something like:

str = Replace(str, vbCrLf, "+")

answered Nov 12, 2015 at 20:14

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Comments

Marc · Accepted Answer · 2015-11-12 20:16:25Z

0

Would a formula like this in column J not suffice?

=LEFT(H2,FIND(" ",H2)-1)

It extracts all characters prior to the first space character.

Same approach in VBA like this:

Sub test1()
   Dim mystring As String
   Dim newstring As String

   mystring = "1.2 million +" 'hard-coded string
   newstring = Left(mystring, InStr(1, mystring, " ") - 1) 'alteration of my formula suggestion
   MsgBox (newstring) 'see the output

End Sub

edited Nov 12, 2015 at 20:16

answered Nov 12, 2015 at 20:09

Marc

11.6k2 gold badges40 silver badges48 bronze badges

1 Comment

Stupid_Intern Over a year ago

No because this is part of a macro that has different aim

user4039065 · Accepted Answer · 2015-11-12 22:19:44Z

Rather than looping through the cells, a bulk Range.TextToColumns method would be appropriate as long as you discard the fields of information you do not want.

The sample you supplied has a non-breaking space (e.g. Char(160)) between the number and the 'million'. This is a common anomaly when copying information from a web page. This should take care of that rogue space character as well as regular (e.g. Chr(32)) spaces.

With Worksheets("Sheet9")    '<~~ set this worksheet refernce properly
    With Columns(1)
        .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
                       ConsecutiveDelimiter:=True, _
                       Tab:=False, Semicolon:=False, Comma:=False, _
                       Space:=True, Other:=True, OtherChar:=Chr(160), _
                       FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 9))

    End With
End With

Collectives™ on Stack Overflow

Split not working excel VBA

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related