How to remove spaces in a text without turning it into a number?

Asked

Viewed 663 times

3

I have some codes that at the time of conversion to excel, appear several spaces and need to remove them via vba, but when I use the function Trim the same converts them into number instead of keeping as text.

Example:

1.1

Sub retirar_espaço()
    range("A1").value = worksheetfunction.trim(range("A1"))
End sub

Answer: 1,1

The same happens when I use the replace function:

range("A1").Replace what:=" ", replacement:=""

I would like to know how to perform this operation without converting text to number.

excel-vba

  • Use the method of .NumberFormat or the Excel formula =TEXT() or to convert to String CStr()

1 answer

2


By default, cells have the "General" format type. This type has no specific format, so when performing the TRIM with the cell value only with numerical value (e.g. if the cell value was 1.1 qualquer texto, no problem) Excel converts the semicolon.

Before doing TRIM, you can convert the cell to TEXT type:

Sub retirar_espaço()
    Range("A1").NumberFormat = "@" 'converte para o tipo "texto"
    Range("A1").Value = WorksheetFunction.Trim(Range("A1"))
End Sub

This way there will be no substitution of the semicolon.

Browser other questions tagged excel-vba

You are not signed in. Login or sign up in order to post.