Is it possible replace the value with regex in batch?

[NauticalGent]

If the numbers are always at the start of the address you can use the val and replace functions

str="1246, Cosworth Place"
replace(str,val(str),format(val(str),"#,##0"))

in the immediate window
?replace("1246, Cosworth Place",val("1246, Cosworth Place"),format(val("1246, Cosworth Place"),"#,##0"))
1,246, Cosworth Place

Val()...learn something new every day. Thanks CJ

 

[The_Doc_Man]

An interesting problem because now you add a comma in an inconsistent use, which will make your dataset that much harder to parse later. Let's say your data is street address, city, state, country. Two examples.

Before: 1234 Five Place, BumRush, Louisiana, USA
After: 1,234 Five Place, BumRush, Louisiana, USA

Before: 666 Lucifer Lane, Hell, Texas, USA
After: 666 Lucifer Lane, Hell, Texas, USA

You now have five commas where previously you had four in one record, and ALSO now have a comma that is NOT a sub-address delimiter in the first sample record. But the second sample record has only four sub-addresses and no extra commas. Therefore, this is now an inconsistent data sequence. However, since you are doing this for a certification test, the problem doesn't have to make sense. It just has to be tricky. However, I think if VAL encounters an aberrant address that begins with a letter for some reason, this will come to no good.

The first time you run the suspected records against that "VAL" function, you will get the commas inserted. If you ran the same code the second time, it would do nothing. So the best answer is probably using VAL and extracting the VAL string from the original string and concatenating the altered string. CJ's solution is most likely to do the job.

 

[Gasman]

The first time you run the suspected records against that "VAL" function, you will get the commas inserted. If you ran the same code the second time, it would do nothing.

Not so Doc

? val("1,234")
1

My thoughts were to only process records where Val() is more than 999, so that would get past that issue?

 

[ebs17]

To answer the question more literally:

? RegExReplace("1234 johndon street", "(\d+)(\d{3})", "$1,$2")
1,234 johndon street

' for examble in a query
SELECT RegExReplace(D.Proadress, "(\d+)(\d{3})", "$1,$2") AS Proadress FROM tblData AS D

Private pRegEx As Object

Public Property Get oRegex(Optional Reset As Boolean) As Object
   If (pRegEx Is Nothing) Then Set pRegEx = CreateObject("Vbscript.Regexp")
   If Reset Then Set pRegEx = Nothing
   Set oRegex = pRegEx
End Property

Public Function RegExReplace(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      ByVal ReplaceText As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As String
 
   With oRegex
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .MultiLine = bMultiLine
      RegExReplace = .Replace(SourceText, ReplaceText)
   End With
End Function

However, @CJ_London's proposal is slimmer and preferable.

 

[CJ_London]

My thoughts were to only process records where Val() is more than 999, so that would get past that issue?

My suggestion assumes a one time pass. If this is a regular thing then you modify the formula slightly

str="1,246, Cosworth Place"
replace(str,val(replace(str,",","")),format(val(replace(str,",","")),"#,##0"))


?replace("1,246, Cosworth Place",val(replace("1,246, Cosworth Place",",","")),format(val(replace("1,246, Cosworth Place",",","")),"#,##0"))
1,246, Cosworth Place

and without a comma in the number
?replace("1246, Cosworth Place",val(replace("1246, Cosworth Place",",","")),format(val(replace("1246, Cosworth Place",",","")),"#,##0"))
1,246, Cosworth Place

and for numbers <1000
?replace("123, Cosworth Place",val(replace("123, Cosworth Place",",","")),format(val(replace("123, Cosworth Place",",","")),"#,##0"))
123, Cosworth Place

 

[ebs17]

The variations are then no longer so slim.
As requirements increase, the appeal of a RegEx solution increases.

 

[The_Doc_Man]

Not so Doc

? val("1,234")
1

My thoughts were to only process records where Val() is more than 999, so that would get past that issue?

Hmmm... the VAL function sees comma-delimited strings that way? That one is unexpected. And proof why the given problem is actually more insidious than I first thought. I have a way to do it reliably using a text parser module (found elsewhere in Code Samples section) but it becomes a complex function very quickly and the OP is trying to avoid complexity.

 

[sleepy11]

If the numbers are always at the start of the address you can use the val and replace functions

str="1246, Cosworth Place"
replace(str,val(str),format(val(str),"#,##0"))

in the immediate window
?replace("1246, Cosworth Place",val("1246, Cosworth Place"),format(val("1246, Cosworth Place"),"#,##0"))
1,246, Cosworth Place

Thanks for your reply. I will start to get familiar with the function part.

 

[jdraw]

Not so Doc

? val("1,234")
1

My thoughts were to only process records where Val() is more than 999, so that would get past that issue?

Perhaps: More than 999 and Not containing a comma.

 

[Gasman]

Perhaps: More than 999 and Not containing a comma.

The string will always contain commas though?, for the rest of the address.

 

[jdraw]

I agree Paul. However I was just working with your sample

? val("1,234")

You could devise logic to deal with numeric street address. eg Split and process substring...

 

[DickyP]

I am an enthusiastic user of RegEx in Access, but in this particular case I'd use another tool to edit/transform the data first, such as PowerGrep where you can keep playing with the data until you get it right.

 

[CJ_London]

@jdraw , @The_Doc_Man - see post #25 for the solution for 1,234