Extracting and then Converting a 7 digit number to a 10 digit number within a text string

[kawliga]

Hello,

We have large files that require we change customers 7 digit call forwarding number to a new 10 digit number (adding an area code). I have tried severl formulas but so far no luck.

Example:

CWT DGT PIC 0222 Y CFDA N NSCR 1 A 24 FIXRING 5936245 CFBL N NSCR 1 A

In this example I would like to extract and change the 5936245 to 3155936245.

The 7 digit number is not always the same so I need to extract ANY 7 digit number and then add the 315.

Thanks In Advance

 

[kawliga]

Not sure if this has any impact but there will not ALWAYS be a "CF" in Column A and/or Column B.

Thanks Again

 

[Rick Rothstein]

Rick, Thanks but it doesn't seem to be working. See my example below.

Column A = 5923391
Column B = CWT 3WC DGT PIC 0893 Y CFW C NSCR 1 I $ CFDA N NSCR 1 A 24 FIXRING 5936245
Column C = CFBL N NSCR 1 A 5936245 LPIC 0893 Y
Column D = 5923391 (formula view=CFs(A35:C35)

I was under the impression that you would had already converted your 7-digit numbers to 10-digit numbers before you ran my code (at least that is how I read the progression of messages in this thread, particularly the wording of Message #8). Is that not the case any more?

 

[kawliga]

Oh!

So sorry Rick, I was going to run that part first ! Is there a way to integrate the 7 digit change to 10 along with this? If not I can of course run the 1st VBA then this one

Thanks

 

[kawliga]

Rick,

Yes. When I run the VBA to convert to 10 digit and then run your VBA it works!!!

 

[kawliga]

Hello all,

I've just found another issue, I have a few records where the CF number will not be 7 digits but will be 8 digits (almost always beginning with a "9"). I was able to manipulate the VBA to find any "8" digit number but the results then are 31595925390 as opposed to the desired 93155925390. Is there a way to include parameters to find and change not only the 7 digits to 315xxxxxxx but also these random 8 digit numbers to 93155925390?

Thanks Again