Find entries lacking prefix and then add prefix.

[gsteel]

I have a column with 6000 entries. Most start with XXXX then a part number. I need to change the prefix which I did using the Substitute function. Is there away to search out the entries that had no prefix and add the new prefix? Like an if not then concatenate perhaps?


I suppose I could have stripped all old prefixes where found. Then concatenate the new prefix to all entries. Is that the best method?

[NBVC]

You can add a formula in an adjacent column like:

=IF(LEFT(A1,4)<>"XXXX","XXXX"&A4,A4)

copied down.

You can then copy this column and Paste Special >> Values over the original column and then delete the formula column.

[gsteel]

Many Thanks