Use cell value as string VBA

Question

Cell A2 contains value "25-86".

I need cell B2 to have value "AJ 2586%"

This code reveals Syntax error:

Range("B2").Value = "AJ & Left(Range("A2"), 2) & Right(Range("A2"), 2) & %"

If I write it like that

Range("B2").Value = "AJ & Left(A2, 2) & Right(A2, 2) & %"

The functions Left and Right treat "A2" as string.

How could I extract a part of text from the cell and enter it in another cell?

Solution 1

The issue is that everything between the " " defines a string. You can type your code like this:

Range("B2").Value = "AJ " & Left(Range("A2"), 2) & Right(Range("A2"), 2) & "%"

Solution 2

The following formula will return the value as a real percentage that you can use in calculations.
It just removes the - from the figure.

=SUBSTITUTE(A2,"-","")/100  

Initially it will display the value as 25.86.

When you apply the custom number format of:

\AJ 0%  

It will then display as AJ 2586%.

If that formula is in A1 then =A1/2 will return AJ 1293%.

Edit:
Just realised you want it in VBA:

Range("B2") = Replace(Range("A2"), "-", "") / 100
Range("B2").NumberFormat = "\AJ 0%"

Or, if you just want it as text:

Range("B2") = "AJ " & Replace(Range("A2"), "-", "") & "%"

Solution 3

Range("B2").Value = "AJ " + Left(Range("A2"), 2) + Right(Range("A2"), 2) + "%"

Indent code 4 spaces in order for it to be in the correct format

Comments

[cmbarnett87]

If you check my code in VBA it works as expected (as + can concatenate without the need of an ampersand). Either way works.

[urdearboy]

Type Mismatch

[Scott Craner]

@cmbarnett87 if either of the items being concatenated are numbers then vba will try to add the number to the text string and you will get Type Mismatch. If both are strings then yes it works, but & works will both numbers and strings. So to avoid the confusion and errors it is generally accepted that one should use & instead of + where concatenating.

[cmbarnett87]

I think I might have missed that his example had double quotes (which I'm assuming he put in the cell as well?). When I tested it in Excel I didn't encounter any issues with the + or & - again, that was without double quotes.