Preserving Superscript when using CONCATENATE

[NEDDY]

Hi,

Trying to CONCATENATE 2 text columns, (A1 -AX & B1 - BX), column A has a single superscript character in the middle of the word, i.e. xxxx2xxxx , where the 2 superscript. The column B has no formatting to preserve. I have found the following link with VBA code to assist, but can't seem to get it to work.

http://www.mrexcel.com/archive/VBA/7406.html

Any other methods out there, or what is required to make the linked VBA code work correctly?

Thanks

 

[Peter_SSs]

Hi,

Trying to CONCATENATE 2 text columns, (A1 -AX & B1 - BX), column A has a single superscript character in the middle of the word, i.e. xxxx2xxxx , where the 2 superscript. The column B has no formatting to preserve. I have found the following link with VBA code to assist, but can't seem to get it to work.

http://www.mrexcel.com/archive/VBA/7406.html

Any other methods out there, or what is required to make the linked VBA code work correctly?

Thanks

Welcome to the MrExcel board!

Somehow there needs to be a way to identify what is to be 'superscripted'.

1. Is it always just a single character and only one character in the cell?
2. Is it always the 5th character, or always the first numerical digit etc?

 

[NEDDY]

Welcome to the MrExcel board!

Somehow there needs to be a way to identify what is to be 'superscripted'.

1. Is it always just a single character and only one character in the cell?
2. Is it always the 5th character, or always the first numerical digit etc?

------------------------------------------------------------------
Peter,

It is only one character in the cell, and it is always the 11th character, including spaces, decimals etc.

Thanks

 

[Peter_SSs]

------------------------------------------------------------------
Peter,

It is only one character in the cell, and it is always the 11th character, including spaces, decimals etc.

Thanks

In that case, try something like this. Wasn't sure if you wanted the space between the two concatenated values or not.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Superscript()<br>    <SPAN style="color:#00007F">Dim</SPAN> Crange <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Crange = Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Crange<br>        .FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Crange<br>        c.Characters(START:=11, Length:=1).Font.Superscript = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>