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]

No, there should not be any other "CF" text prior to the initial "CF"

 

[Rick Rothstein]

No, there should not be any other "CF" text prior to the initial "CF"

Sorry, one more question... where is your existing data located at (Column A perhaps) and where should the results... text with 315 prepended (Column B perhaps) and the text that starts with CF, especially if more than one (Columns C, D, etc. perhaps) go at?

 

[kawliga]

No other CF text prior to the first

Thanks

 

[Rick Rothstein]

No other CF text prior to the first

That does not answer the question I asked in Message #12.

 

[kawliga]

They are in 3 columns and would like the result to be in Column D


Example:


A B C
4022311 CWT 3WC DGT SUPPRESS PUBLIC Y Y PIC 0893 Y SC2 CFW C NSCR 1 I $ CFDA N NSCR 1 A 24 FIXRING 5936245 CFBL N NSCR 1 A 5936245 LPIC 0893 Y

 

[kawliga]

They are in 3 columns and would like the result to be in Column D

Column A has the Phone Number
Column B has one string with a possible CF
Column C has one string with a possible CF

Results in Column D would be great

Thanks

 

[Rick Rothstein]

They are in 3 columns and would like the result to be in Column D

Column A has the Phone Number
Column B has one string with a possible CF
Column C has one string with a possible CF

Results in Column D would be great

They are in 3 columns and would like the result to be in Column D

Example:

A B C
4022311 CWT 3WC DGT SUPPRESS PUBLIC Y Y PIC 0893 Y SC2 CFW C NSCR 1 I $ CFDA N NSCR 1 A 24 FIXRING 5936245 CFBL N NSCR 1 A 5936245 LPIC 0893 Y

I thought the phone number(s) was embedded in the long text string? While I know everything about your data is clear to you, it is not clear to me at all... I am not clear on what your existing layout is nor what your final layout needs to be nor when our proposals should be applied. Unfortunately your example does not display in a manner that is easy to see which text belongs to which column. Instead of listing across, list down showing the value in each cell that way. For example...

A1: Value in Column A Row 1
B1: Value in Column B Row 1
C1: Value in Column C Row 1
D1: Result you want from the above

In the above listing, please make clear what your original data (before prepending) looks like. If you are overwriting existing data, then show us two listings like above... one for existing data before anything is done to it and a second one showing what you want the final result(s) to look like.

 

[kawliga]

Of course

[TABLE="width: 634"]
<tbody>[TR]
[TD]A1: Value in Column A Row 1[/TD]
[TD]5924124[/TD]
[/TR]
[TR]
[TD]B1: Value in Column B Row 1[/TD]
[TD]CWT 3WC DGT PIC 0893 Y SC2 CFW C NSCR 1 I 3153827146 PIC[/TD]
[/TR]
[TR]
[TD]C1: Value in Column C Row 1[/TD]
[TD]CFB N 3155936245 A 1 CBU CFD N 3155936245 A 1 CDU[/TD]
[/TR]
[TR]
[TD]D1: Result you want from the above[/TD]
[TD]5924124 CFW C NSCR 1 I 3827146 CFB N 3155936245 CFD N 3155936245[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[Rick Rothstein]

Of course

[TABLE="width: 634"]
<tbody>[TR]
[TD]A1: Value in Column A Row 1[/TD]
[TD]5924124[/TD]
[/TR]
[TR]
[TD]B1: Value in Column B Row 1[/TD]
[TD]CWT 3WC DGT PIC 0893 Y SC2 CFW C NSCR 1 I 3153827146 PIC[/TD]
[/TR]
[TR]
[TD]C1: Value in Column C Row 1[/TD]
[TD]CFB N 3155936245 A 1 CBU CFD N 3155936245 A 1 CDU[/TD]
[/TR]
[TR]
[TD]D1: Result you want from the above[/TD]
[TD]5924124 CFW C NSCR 1 I 3827146 CFB N 3155936245 CFD N 3155936245[/TD]
[/TR]
</tbody>[/TABLE]

This UDF should generate the values to populate Column D. Simply pass it the range for Columns A thru C. So, for the first row, the formula in Column D would be...

=CFs(A1:C1)

Here is the UDF code to install in your general module...

[table="width: 500"]
[tr]
	[td]Function CFs(Rng As Range) As String
  Dim X As Long, Z As Long, CF() As String
  CFs = Rng(1).Value
  CF = Split(" " & Application.Trim(Join(Application.Index(Range(Rng(2), Rng(Rng.Count)).Value, 1, 0))), " CF", , vbTextCompare)
  For X = 1 To UBound(CF)
    For Z = 1 To Len(CF(X))
      If Mid(CF(X), Z, 10) Like "##########" Then
        CFs = CFs & " CF" & Left(CF(X), Z + 9)
        Exit For
      End If
    Next
  Next
End Function[/td]
[/tr]
[/table]

 

[kawliga]

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)

Thanks Again