CODEWITHSUNDEEP
excelvbatextformatnumeric
Angel
Angel

Reputation: 1980

VBA change text to number format

In vba I want to do some replacement, but Excel breaks the format when see something like a number.

I need 2 kinds of replacements

First need:

Original Data      =>   Wanted Data   =>  Best Approach   
['4', 6, '4']      =>   4,6,4         =>  4#6#4
[1.5, None, None]  =>   1.5           =>  1.5##
[256, 256]         =>   256,256       =>  256#256
[None, '', None]   =>   ,,            =>  ##
[4, 4]             =>   4,4           =>  4#4
[None, '1.50']     =>   1.5           =>  #1.5

The wanted Data is impossible to obtain, because the wanted data 256,256 is automatically convert in 256.256

The approach is work fine for me, because I can do an split with # as a separator

With RNG
        .Replace What:="None", Replacement:=""
        .Replace What:=",", Replacement:="#"
        .Replace What:="[", Replacement:=""
        .Replace What:="]", Replacement:=""
        .Replace What:="'", Replacement:=""
        .Replace What:=" ", Replacement:=""
End With

Second need (it will be only 1 float in the string):

Original Data   =>   Wanted Data      
[None, '1.5']   =>   1.5      (breaks into 1,5)                  
[1.50]          =>   1.50     (breaks into 1,5) 
0.9,            =>   0.9      (breaks into 0,9)      
0.6             =>   0.6      (doesn't break because any replaced has been made)

With RNG
        .NumerFormat = "@" 'Doesn't work, although I do not want it either text format
        .Replace What:="None", Replacement:=""
        .Replace What:=",", Replacement:=""
        .Replace What:="[", Replacement:=""
        .Replace What:="]", Replacement:=""
        .Replace What:="'", Replacement:=""
        .Replace What:=" ", Replacement:=""
End With

Any idea for the second need?

The next option to solve the second need "works fine" but is too slowly (more than 10000 rows) and even block my Excel during the process

For Each CL In RNG
        CL.NumberFormat = "@"
        CL.Value = Replace(CL.Value, ",", "")
        CL.Value = Replace(CL.Value, "None", "")
        CL.Value = Replace(CL.Value, "[", "")
        CL.Value = Replace(CL.Value, "]", "")
        CL.Value = Replace(CL.Value, "'", "")
        CL.Value = Replace(CL.Value, " ", "")
Next CL

Thanks a lot

Upvotes: 0

Views: 1209

Answers (2)

JvdV
JvdV

Reputation: 75960

You can enforce the text format like so:

Sub Test()

Dim RNG As Range, CL As Range
Dim LR as Long

'Get last used row in your sheet and set range (use a different type of lookup for LR if you want.
LR = Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
Set RNG = Sheets(1).Range(Cells(1, 14), Cells(LR, 16))

'Set values to text and replace values
With RNG
    .NumberFormat = "@"
    .Replace What:="[", Replacement:=""
    .Replace What:="]", Replacement:=""
End With

'Trim all values
For Each CL In RNG
    CL.Value = Replace(CL.Value, " ", "")
Next CL

End Sub
  • Try to avoid .select and Selection
  • Obviously change the RNG to your needs.

NOTE: If you don't care about the comma you could replace that with a "-"

Sub Test()

Dim RNG As Range
Dim LR As Long

'Get last used row in your sheet and set range (use a different type of lookup for LR if you want.
LR = Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
Set RNG = Sheets(1).Range(Cells(1, 14), Cells(LR, 16))

'Set values to text and replace values
With RNG
    .NumberFormat = "@"
    .Replace What:="[", Replacement:=""
    .Replace What:="]", Replacement:=""
    .Replace What:=" ", Replacement:="-"
    .Replace What:=",", Replacement:=""
End With

End Sub

In this case I don't think numberformat would be nesseccary at all.

Upvotes: 1

Shyam Pandey
Shyam Pandey

Reputation: 62

You are Using replace function which is basically belong from text category.So you can multiply your result with 1 or you can use value function.

Upvotes: 0

Related Questions