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?

Answer 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) & "%"

Answer 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"), "-", "") & "%"