Find decimal-formatted and replace with % in a text cell? Maybe RegEx?

LittleGriff

New Member
Joined
Jan 13, 2012
Messages
11
Hello! I have been working on a report, manipulating and formatting various features using VBA. I received a request I haven't heard before.

The exported file contains several columns that contain percentage-formatted values of three or four digits. I'm being asked to join those with text from adjoining text columns, and format the numeric value as a percentage. Of course, when I do the join the percentage is converted to decimals and becomes a string.

The only thing I can think of to try is: find a way to convert the percentage to a string WITH the decimal and % sign in the right places before I do the join, but the numbers in those columns vary widely -- so I can't find and replace individual values. I'm no expert on wildcards, or RegEx, if that's the solution.
Here's a sample of what I get in the raw worksheet:
1.75% (formatted as Percentage) | Of the original amount (this text is in a separate column)

Here's what I get when I do the join:
.0175 (Of the original amount) | (joined into a single cell and I add the parens to the string)

Here's what I'm being asked for:
1.75% (Of the original amount)

Is this even possible?

Thank you so much!
 

LittleGriff

New Member
Joined
Jan 13, 2012
Messages
11
Thanks for your quick reply! I should have included a little code; I'm not seeing a way to use a function to accomplish the formatting.

I'm looping through each row of the columns, creating the join as below, then assigning newString back to overwrite the first cell ("BQ" & i in this case). Let me know if I need to include more code to make my goal clearer.

I have read that Text is a property of the Range object, but I'm having trouble finding examples of Range.Text used "in the wild."

<code>
newString = Worksheets("TEST").Range("BQ" & i).Value & " (" & Worksheets("TEST").Range("BR" & i).Value & ")"
</code>

Thanks again.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,666
Office Version
2007
Platform
Windows
Try:

Code:
newString = [COLOR=#0000ff]Format[/COLOR](Worksheets("TEST").Range("BQ" & i).Value, "0.00 %") & " (" & Worksheets("TEST").Range("BR" & i).Value & ")"
 

LittleGriff

New Member
Joined
Jan 13, 2012
Messages
11
This did the trick! Thank you so much. I was really in the weeds on the problem; just could not solve it alone.

Much appreciated!

Maria
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,666
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,077,635
Messages
5,335,379
Members
399,014
Latest member
hamzalaarif

Some videos you may like

This Week's Hot Topics

Top