Dave T
05-22-2014, 05:28 PM
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
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