CODEWITHSUNDEEP
excelvba
Jeff
Jeff

Reputation: 30

Insert text after 4 decimal variable number in Excel VBA

I have been working on a processing file in Excel VBA to take raw data from our very old database software and arrange the .txt output into an Excel database. The problem I am running into is that I need to add a delimiting marker after a number sequence to tell Excel to split this part of the text string into two columns. The number sequence ranges from 0.0000 to 999.9999 and I need to keep this number intact. The number is always represented with four decimal points.

Could someone please give me some direction on how to accomplish adding a character (like Æ) after a variable number sequence for all cells within a column?

Raw data:

A1: Hello World, This is a test of 22.5010 and is only a test

A2: Hello World, This is a test of 0.1250 and is only a test

A3: Hello World, This is a test of 0.0000 and is only a test

Final Result:

A1: Hello World, This is a test of 22.5010Æ and is only a test

A2: Hello World, This is a test of 0.1250Æ and is only a test

A3: Hello World, This is a test of 0.0000Æ and is only a test

Thank you in advance!

Upvotes: 1

Views: 988

Answers (2)

Sifu
Sifu

Reputation: 1082

Don't forget to add the proper references to the project (in tab Tools>References):
Add "Microsoft VBScript Regular Expressions 5.5"

Reference

Now add a button onto your excel page and double click on the button to open the proper macro page. Add his code to the macro page it just opened:

Sub Button1_Clic()
'
' Button1_Clic Macro
'

'This sub will scan through the first column (A) and add Æ to the end of
'numbers with the proper format.

On Error Resume Next 'Needed incase the line doesn't have a number

Dim i As Integer
Dim NumberStr As Object

Dim RegEx As RegExp
Set RegEx = New RegExp
With RegEx
    .IgnoreCase = True
    .Global = False
    .Pattern = "\d{1,3}\.\d{4}"
End With

For i = 1 To 500 'Change last value to the last row in the column

    Set NumberStr = RegEx.Execute(Range("A" & i).Value)
    Range("A" & i).Value = RegEx.Replace(Range("A" & i).Value, NumberStr(0).Value & "Æ")


Next i

End Sub

This regex "\d{1,3}\.\d{4}" reads :
- Numeric character between 1 to 3 times repeated \d{1,3}
- Dot \.
- Numeric character repeated 4 times \d{4}

More info on Regexes.

Upvotes: 1

Michael
Michael

Reputation: 111

This will work even if a period occurs after the number.
This won't work if the period occurs before the number

Before:

A1: Hello World, This is a test of 0.1250 and is only a test
A2: Hello World, This is a test of 0.1250 an.d i.s on.ly a te.st
A3: Hello W.orld, This is a test of 0.1250 and is only a test

After:

A1: Hello World, This is a test of 0.1250Æ and is only a test
A2: Hello World, This is a test of 0.1250Æ an.d i.s on.ly a te.st
A3: Hello W.orldÆ, This is a test of 0.1250 and is only a test

Code: 

For cell = 1 To 3 ' ENTER MAX ROW
    Range("A" & cell).Value = Left(Range("A" & cell).Value, InStr(Range("A" & cell).Value, ".") + 4) & "Æ" & Right(Range("A" & cell).Value, (Len(Range("A" & cell).Value) - 4) - InStr(Range("A" & cell).Value, "."))
Next cell

Upvotes: 1

Related Questions