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

[LittleGriff]

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!

 

[mikerickson]

Try =TEXT(A1, "0.00 %") & "(" & B1 &")"

 

[LittleGriff]

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]

Try:

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

 

[LittleGriff]

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]

I'm glad to help you. Thanks for the feedback.