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

 

[GTO]

Does it have to be formula-driven or would VBA suffice?

 

[kawliga]

Unfortunately I am not experienced in VBA so a formula if possible

Thanks

 

[Joe4]

Are the text before or after the number always same, i.e. FIXRING or CFBL?
Or is there always the same number of words/terms before or after the number?

By the way, you don't necessary need to know how VBA works to use it. If someone came up with some code for you, all you would have to do is run the code.

 

[GTO]

Hi again,

I'm about to log out, so here's a shot with a UDF. As Joe mentions, you might be able to use it without really studying it.

Whilst staring at the worksheet, press ALT + F11. You are now looking at the visual basic editor (VBE) window. From the menu bar, select Insert|Module. In the created module, paste:

Option Explicit
  
Public Function PREPEND(ByVal Cell As Range)
Static REX As Object
  
  If REX Is Nothing Then
    Set REX = CreateObject("VBScript.RegExp")
  End If
  
  With REX
    .Global = True
    .Pattern = "\b[0-9]{7}\b"
    If .Test(Cell.Value2) Then
      PREPEND = .Replace(Cell.Value2, "315$&")
    Else
       PREPEND = Cell.Value2
    End If
  End With
  
End Function

Now in the cell's you want the return in, just use it like a regular formula:

Sheet2[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]Header[/TD]
[TD]Header[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]CWT DGT PIC 0222 Y CFDA N NSCR 1 A 24 FIXRING 5936245 CFBL N NSCR 1 A[/TD]
[TD]CWT DGT PIC 0222 Y CFDA N NSCR 1 A 24 FIXRING 3155936245 CFBL N NSCR 1 A[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]CWT DGT PIC 0222 Y CFDA N 5936245 NSCR 1 A 24 FIXRING 5936245 CFBL N NSCR 1 A[/TD]
[TD]CWT DGT PIC 0222 Y CFDA N 3155936245 NSCR 1 A 24 FIXRING 3155936245 CFBL N NSCR 1 A[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
B2	=PREPEND(A2)
B3	=PREPEND(A3)

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]



No "swearsies", but I think that should catch any 7-digit number.

Hope that helps,

Mark

 

[kawliga]

GTO/Mark,

Thanks so much for walking me through this! It looks to be working!

 

[GTO]

You are most welcome

 

[kawliga]

so I did have another issue now. I am now wanting to extract from a string the " cf " (there are multiple CF naming conventions, but they all do start with CF) and then the rest of the string up to the 10 digit number. Basically I am trying to extract the cf and what it's forwarded to but remove all the other "garbage".


Example:


PIC 0893 Y LPIC 0893 Y MWT MWL N N CFU N 95923433 I 1 CFB N 95936245 A 1


I'd like to pull only the :


CFU N 3155923433


and the


CFB N 3155936245


If I need to "run" something twice to get both instances (CFU and CFB) that's no problem


Any help would be great

 

[Rick Rothstein]

Option Explicit
  
Public Function PREPEND(ByVal Cell As Range)
Static REX As Object
  
  If REX Is Nothing Then
    Set REX = CreateObject("VBScript.RegExp")
  End If
  
  With REX
    .Global = True
    .Pattern = "\b[0-9]{7}\b"
    If .Test(Cell.Value2) Then
      PREPEND = .Replace(Cell.Value2, "315$&")
    Else
       PREPEND = Cell.Value2
    End If
  End With
  
End Function

I believe this non-RegExp UDF (user defined function) will produce the same results as your code above does...

[table="width: 500"]
[tr]
	[td]Function Prepend(S As String) As String
  Dim Number As String
  If S = "" Then Exit Function
  Number = Evaluate("MAX(IFERROR(0+MID(SUBSTITUTE(""" & S & """,""."",""X""),ROW(1:" & Len(S) & "),7),0))")
  Prepend = Replace(S, Number, "315" & Number)
End Function[/td]
[/tr]
[/table]

 

[Rick Rothstein]

so I did have another issue now. I am now wanting to extract from a string the " cf " (there are multiple CF naming conventions, but they all do start with CF) and then the rest of the string up to the 10 digit number. Basically I am trying to extract the cf and what it's forwarded to but remove all the other "garbage".

Since you have text in front of the text you want to extract from, we need to know if any of that text could ever start with the letters CF? If so, then is the CF code you are looking for always 3 characters long and, if so, can any other CF text be 3 characters long as well?


Side Note: Given that you could read this posting and miss earlier ones, I just wanted to draw your attention to the fact that I posted an alternate VB solution in Message #9 for you to consider.