Find PN then trim all spaces

[Dave T]

Hello All,

I have a spreadsheet where photos taken from inspections are entered.
The heading for each inspection is uppercase ‘PN’ followed by a series of numbers i.e. PN1234, where there could be up to twelve numbers after the PN prefix. The PN number are only entered in column B in a worksheet called 'FINAL' from cell B3 down.
Within a worksheet there may be multiple inspections entered, hence multiple instances of headings with the PN prefix.

Another program searches for PN in the title in a CSV file created by the worksheet and then imports the respective photos into the database.

My problem is that some users may enter spaces before the prefix PN or enter spaces between PN and the numbers or a enter spaces after the numbers.

I am after a macro that will search for the uppercase PN in column B from B3 down and then remove all spaces (either before, mid or end) from that cell so the end result would be PN1234.

Any help would be appreciated.

Regards,
Dave T

[ashleyuk1984]

See if this works for you.

Sub PN()
Dim LastRow As Long
Dim x As Long

LastRow = Range("B" & Rows.Count).End(xlUp).Row

For x = 3 To LastRow
    If InStr(1, Range("B" & x).Value, "PN") Then Range("B" & x).Replace What:=" ", Replacement:=""
Next x

End Sub

[Dave T]

Hello ashleyuk1984,

Works perfectly....
I really appreciate your help.

I did an internet search and found many macros that would remove all spaces, but I could not find any that searched for a specific prefix.

Thanks again for your help.

Regards,
Dave T