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

Question

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

Navigation

#1 by (2 votes)

3

I have some code that at the time of conversion to excel, several spaces appear and I need to remove them via vba, but when I use the trim function it converts them to number instead of keeping it 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 from text to number.

excel-vba

asked by anonymous 02.04.2018 / 20:40

1 answer

Serialize with pure JavaScript What is ASP.NET Core Blazor?

score 2 · Accepted Answer

By default, the cells have the "General" format type. This type does not have a specific format, so when performing TRIM with the value of the cell with only numeric value (eg, if the cell value was 1.1 qualquer texto , that would not be a problem) Excel converts a semicolon. p>

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 will not replace the semicolon.