Delete the spaces in the middle and end of a certain string from a website

Question

When I extract a string from a website the textbox shows:

<!-- -->  <!--  Principal - CIIU 64207 - TELECOMUNICACIONES -->         <!--    -->     6190 - OTRAS ACTIVIDADES DE TELECOMUNICACIONES

I use this code to delete the lines and spaces

Texto = Trim(Replace(Trim(Replace(Trim(Tabla(24)), "-->", "")), "<!--", ""))
.Txt_Act_Economica.Value = Trim(Texto)

The result is:

Principal - CIIU 64207 - TELECOMUNICACIONES                     6190 - OTRAS ACTIVIDADES DE TELECOMUNICACIONES

The Function Trim doesn't delete the spaces in the middle and in the end of the string.

Trim in VBA only removes spaces at the ends of string - it will not do anything in the middle of a string. Also it's possible you don't have actual spaces but "non-breaking spaces" `Chr(160)` So try `Replace(Tabla(24), Chr(160), " ")` to convert those to regular spaces first. — Tim Williams, Jun 15 '20 at 04:36
I expect this "Principal - CIIU 64207 - TELECOMUNICACIONES 6190 - OTRAS ACTIVIDADES DE TELECOMUNICACIONES" but now I see that the website shows diferent information like this " 4690 - VENTA AL POR MAYOR NO ESPECIALIZADA 4799 - OTRAS ACTIVIDADES DE VENTA AL POR MENOR NO REALIZADAS EN COMERCIOS, PUESTOS DE VENTA O MERCADOS 4922 - OTRAS ACTIVIDADES DE TRANSPORTE POR VÍA TERRESTRE" — Pilar Giannine Valera Fernndez, Jun 15 '20 at 06:26
So as per my previous comment: `Texto = Application.Trim(Replace(Replace(Tabla(24), "-->", ""), " — JvdV, Jun 15 '20 at 06:30
@Jvdv "Texto = Application.Trim(Replace(Replace(Tabla(24), "-->", ""), " — Pilar Giannine Valera Fernndez, Jun 15 '20 at 06:42
You use backticks around the text you want in grey. Also my suggestion should work fine if there are actual spaces in your text. You might want to find out what those characters really are. Maybe `Application.Clean` would be usefull too. — JvdV, Jun 15 '20 at 06:44

score 2 · Accepted Answer · answered Jun 15 '20 at 06:53

From the comments, the following wouldn't quite get rid of all whitespaces:

Texto = Application.Trim(Replace(Replace(Tabla(24), "-->", ""), "<!--", ""))

That would indicated other type of whitespace characters, where Application.Clean would be usefull:

Texto = Application.Clean(Replace(Replace(Tabla(24), "-->", ""), "<!--", ""))

Just to be sure of removing spaces you can always nest this into Application.Trim:

With Application
    Texto = .Trim(.Clean(Replace(Replace(Tabla(24), "-->", ""), "<!--", "")))
End with

^{Note: In contrast to vba's Trim(), Application.Trim() does get rid of unnecessary spaces between word.}

Solar Mike · Answer 2 · 2020-06-15T05:11:49.147

0

I tried the mid() function with find(), see what you think, I have not done this in vba just to show other functions:

So, in one function:

=TRIM(MID(A1,FIND("Pri",A1,1),FIND("ES",A1,1)-15))

Edit, well you could look for the < > combination after, like this:

=MID(A1,FIND("Pri",A1,1),FIND(">         <",A1,1)-20)

edited Jun 15 '20 at 05:11

answered Jun 15 '20 at 04:49

Solar Mike

Thank you, but I can't use that function because I extract the data from a website, and anytime I look for information the name of the string "CIIU 64207 - TELECOMUNICACIONES" will change – Pilar Giannine Valera Fernndez Jun 15 '20 at 05:03
@PilarGiannineValeraFernndez see my edit, now looking for the < > combination after that string - does that change as well? – Solar Mike Jun 15 '20 at 05:12

2 Answers2