remove spaces and points in cells with vba

[already]

Hi

I have to remove space and/or points from cells with a VAT number , IBAN and swift in exel sheets. (3 or 4 cells on the sheet) How can I do this with VBA.

Thanks in advance for your help

Regards

Al

 

[Trevor G]

Record a macro to use things like trim and find/replace to remove the unwanted characters.

Maybe easier to help if you could show sample data.

 

[already]

Hi Trevor,
Thanks for your reply. Recording the macro seems not to work in this case. (have already tried)

sample data:

vat number in cell D2 NL123 123 123 should be converted in NL123123123
iban in cell D4 NL 123.123 123 should be converted in NL123123123
etc

Kind regards

Al

 

[Michael M]

I can't see why a macro wouldn't do the job, but have you considered doing a FIND and REPLACE for each cell ?

 

[rustam84ali]

Hi this is my first post to MR. excel board, i hope it will work and help

create this funcition in VBA

Function SuperTrim(Entry)
For i = 1 To Len(Entry)
ThisChar = Mid(Entry, i, 1)
Select Case Asc(ThisChar)
Case 32, 46
Case Else
SuperTrim = SuperTrim & ThisChar
End Select
Next i
End Function

then use this in worksheet =SuperTrim(A4)

 

[Michael M]

Hi rustam and Welcome to the Board !!
I'm pretty sure it doesn't happen very often, that somebody's first post is an answer !!
Well done ! nice work !!

 

[rustam84ali]

Thanks Michael!

 

[already]

Hi Rustam,

Thanks for your solution. But in this case I can't use a function.

I'm collecting many documents based on a template from my colleagues with the data in the wrong format. I may not change the template with the required format. So I'm looking for a macro to change the 4 cells to the required format.

Kind regards

Al

 

[John Davis]

Hi Rustam,

Thanks for your solution. But in this case I can't use a function.

I'm collecting many documents based on a template from my colleagues with the data in the wrong format. I may not change the template with the required format. So I'm looking for a macro to change the 4 cells to the required format.

Kind regards

Al

Here is a snippet I use often. Can you use it?

LASTROW = Cells(Rows.Count, 1).End(xlUp).Row

Range("D2:D" & LASTROW).Replace What:=".", Replacement:="", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
     ReplaceFormat:=False

Range("D2:D" & LASTROW).Replace What:=" ", Replacement:="", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
     ReplaceFormat:=False

 

[already]

Hi John,

Wooooow!!!!!! This is it.

Many Thanks

Kind regards

Al