Excel: Find/Replace Wildcard Question

[Airbiskit]

Hi,

I have column that has an array of different numbers that denote order references, these numbers range in size.

The first 2 digits of each number indicates the location where the order was placed.

If I have a number in this column that begins with "20" I would like to do a find and replace this number with the location "Manchester"

Is there a wildcard symbol that looks just at the first 2 digits regardless of number length and would allow the change to be made?

Many Thanks

 

[xlbo]

Sorry but no

Your best option is to create a lookup table of codes and location names

You can then use a formula such as:

=Vlookup(Left(A2,2),LookupTable,2,false) & Right(A2,Len(A2)-2)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Have you tried using * wildcard? Like Edit/Replace 20* with Manchester.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Airbiskit]

Thanks Geoff, tried using that formula on a test lookup table and couldn't get it to work. Would it be something to do with the Order refernce field being all numbers and not a string?

Glenn, like this solution as I can record a macro from this but the problem with this one is that I need the Find to locate the beginning of the order reference. Tested this and it picks up on all 20's regardless of where they appear in the number. Can anything be added to this so it only picks up on the first 2 numbers?

Thank you both for your answers

 

[xlbo]

Airbiskit - most likely yes. If you have numbers in your lookup table, try this amendment:

=Vlookup(VALUE(Left(A2,2)),LookupTable,2,false) & Right(A2,Len(A2)-2)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Airbisket, tick the Find Entire Cells Only checkbox.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Airbiskit]

Thank you both very much. I will try these out.

Regards