Replace values in cells in one column

  • Thread starter Thread starter Vads
  • Start date Start date
V

Vads

Working at a helpdesk, I've got another "weird" request from a user.

I think this should be solved with a macro... But you may change m
mind!

User has data that is exported daily into Excel.
Cells in column J sometimes contain a 10-digit number, which has dat
in front and after it.

By example, the contents of the cells in column J are (starting wit
cell C2):
#
PO3 4400456448 MAGASIN
#
P03 PO 4400473836 SE CREATE D.PETIT
#
P03 4400421747 ESCUDIER V. PO GROUP 948
P03 4400404946 RINALDI J. VAUCLIN J.
PO3 4400451705 MAGASIN
P12 NEEDS ENTRIES ON PO 4400457399 /A. DJELLALI
ITEM 6 & 7 IN DISPUTE PO 4400456125
P12 NEEDS ENTRIES PO 4400457399/A. DJELLALI
P03 4400474550 MAGASIN
PO3 4400434313 NANNINI C.
NEED SE 4400395271 C HARDY
#
P03 4400464433 MAGASIN
P03 4400468148 MAGASIN
PO3 4400444063

The user wants that if a cell that contains a 10-digit number, startin
with 44, everything in front AND after it is removed, thus leaving
cell with a 10-digit number.
By example, the cell containing "PO3 4400456448 MAGASIN" must show th
content "4400456448" after running the macro/formula.

The reason I think this should be solved with a macro, is because thi
needs to be done on a daily basis AND it concerns about >385 cells! N
way it should be done manually.

I know how to make a macro. But what formula should be used in th
macro?

If you're so kind to help me with this, please do so! Thanks i
advance
 
Vads,

A simple formula solution would be
=MID(A1,FIND("44",A1),10)
assuming that the original string is in A1.
You could copy this down for as many rows as you need to.

John
 
Wauw John!

Thanks for this quick solution!!! It's great! I've tried it and i
works!

Thanks!

Vito
 
Just another option, but do Edit / replace on the entire sheet, replacing _44
( _ being a space) with %% 44
Then select the column of data and do Data / text To Columns / Delimited / Tick
Other and put %% in. Hit OK.
 
Back
Top