Align Text on a Specific Character

SledLax · May 9, 2019

Hi.

I want to align the text in a column so that a specific character (±) is in the centre. The number of characters either side will vary depending on the value, # decimal places etc, but the one constant will be the ± character.

This is an example of some data:

44:37 ± 4:01^c

73 ± 11

1.6 ± 0.3^c

7.12 ± 2.40^b

Thanks.

DanteAmor · May 9, 2019

And what is the result you need?

SledLax · May 9, 2019

DanteAmor said:
And what is the result you need?

The result I need is that, for each cell, the ± is in the centre of the cell, irrespective of how many characters either side. That way, all the ± symbols will align vertically as you go down the column.

DanteAmor · May 9, 2019

I expected an image with the result
Of the following options, which one do you want as a result?

Important! In the cell there are already characters that adjust the text in automatic?

shg · May 9, 2019

So it looks like this?

Code:

[size=1][Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"][CENTER]A[/CENTER][/td][td="bgcolor:#C0C0C0"][CENTER]B[/CENTER][/td][td="bgcolor:#C0C0C0"][CENTER]C[/CENTER][/td][/tr][tr][td="bgcolor:#C0C0C0"][CENTER]2[/CENTER][/td][td="bgcolor:#F3F3F3"][CENTER][COLOR=#0070C0]Dim[/COLOR][/CENTER][/td][td="bgcolor:#F3F3F3"][CENTER][COLOR=#0070C0]Tol[/COLOR][/CENTER][/td][td="bgcolor:#F3F3F3"][CENTER][COLOR=#0070C0]Caption[/COLOR][/CENTER][/td][/tr]
[tr][td="bgcolor:#C0C0C0"][CENTER]3[/CENTER][/td][td][RIGHT]5.86[/RIGHT][/td][td][RIGHT]0.023[/RIGHT][/td][td]  5.86 ± 0.023[/td][/tr]
[tr][td="bgcolor:#C0C0C0"][CENTER]4[/CENTER][/td][td][RIGHT]4.14[/RIGHT][/td][td][RIGHT]0.017[/RIGHT][/td][td]  4.14 ± 0.017[/td][/tr]
[tr][td="bgcolor:#C0C0C0"][CENTER]5[/CENTER][/td][td][RIGHT]32.79[/RIGHT][/td][td][RIGHT]0.13[/RIGHT][/td][td] 32.79 ± 0.13[/td][/tr]
[tr][td="bgcolor:#C0C0C0"][CENTER]6[/CENTER][/td][td][RIGHT]744.2[/RIGHT][/td][td][RIGHT]3[/RIGHT][/td][td] 744.2 ± 3[/td][/tr]
[tr][td="bgcolor:#C0C0C0"][CENTER]7[/CENTER][/td][td][RIGHT]12.79[/RIGHT][/td][td][RIGHT]0.051[/RIGHT][/td][td] 12.79 ± 0.051[/td][/tr]
[tr][td="bgcolor:#C0C0C0"][CENTER]8[/CENTER][/td][td][RIGHT]8.78[/RIGHT][/td][td][RIGHT]0.035[/RIGHT][/td][td]  8.78 ± 0.035[/td][/tr]
[tr][td="bgcolor:#C0C0C0"][CENTER]9[/CENTER][/td][td][RIGHT]1.35[/RIGHT][/td][td][RIGHT]0.0054[/RIGHT][/td][td]  1.35 ± 0.0054[/td][/tr]
[/table][/size]

SledLax · May 9, 2019

DanteAmor said:
I expected an image with the result
Of the following options, which one do you want as a result?

Hi Dante.

This is the best way I can show what I'm looking for:

45:26 ± 4:04^c

1.4 ± 0.3^c

5556 ± 457^a

123 ± 9^ac

89 ± 11^ac

69 ± 8

2.0 ± 0.3^ac

<tbody>
</tbody>


What I've done here is manually insert spaces where appropriate so that the ± symbols are aligned vertically (and roughly in the centre), but if there is some way that I can automate this - and make it accurate - that would be great.

Thanks.

SledLax · May 9, 2019

shg said:

So it looks like this?

Code:

[SIZE=1][TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][CENTER]A[/CENTER]
[/TD]
[TD="bgcolor: #C0C0C0"][CENTER]B[/CENTER]
[/TD]
[TD="bgcolor: #C0C0C0"][CENTER]C[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"][CENTER]2[/CENTER]
[/TD]
[TD="bgcolor: #F3F3F3"][CENTER][COLOR=#0070C0]Dim[/COLOR][/CENTER]
[/TD]
[TD="bgcolor: #F3F3F3"][CENTER][COLOR=#0070C0]Tol[/COLOR][/CENTER]
[/TD]
[TD="bgcolor: #F3F3F3"][CENTER][COLOR=#0070C0]Caption[/COLOR][/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"][CENTER]3[/CENTER]
[/TD]
[TD][RIGHT]5.86[/RIGHT]
[/TD]
[TD][RIGHT]0.023[/RIGHT]
[/TD]
[TD]  5.86 ± 0.023[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"][CENTER]4[/CENTER]
[/TD]
[TD][RIGHT]4.14[/RIGHT]
[/TD]
[TD][RIGHT]0.017[/RIGHT]
[/TD]
[TD]  4.14 ± 0.017[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"][CENTER]5[/CENTER]
[/TD]
[TD][RIGHT]32.79[/RIGHT]
[/TD]
[TD][RIGHT]0.13[/RIGHT]
[/TD]
[TD] 32.79 ± 0.13[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"][CENTER]6[/CENTER]
[/TD]
[TD][RIGHT]744.2[/RIGHT]
[/TD]
[TD][RIGHT]3[/RIGHT]
[/TD]
[TD] 744.2 ± 3[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"][CENTER]7[/CENTER]
[/TD]
[TD][RIGHT]12.79[/RIGHT]
[/TD]
[TD][RIGHT]0.051[/RIGHT]
[/TD]
[TD] 12.79 ± 0.051[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"][CENTER]8[/CENTER]
[/TD]
[TD][RIGHT]8.78[/RIGHT]
[/TD]
[TD][RIGHT]0.035[/RIGHT]
[/TD]
[TD]  8.78 ± 0.035[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"][CENTER]9[/CENTER]
[/TD]
[TD][RIGHT]1.35[/RIGHT]
[/TD]
[TD][RIGHT]0.0054[/RIGHT]
[/TD]
[TD]  1.35 ± 0.0054[/TD]
[/TR]
</tbody>[/TABLE]
[/SIZE]

Yes - it would look like column C, but there are also between 0 and 3 superscript letters that get added to SOME values.

e.g.:

89 ± 11^ac

69 ± 8

Fazza · May 10, 2019

as it is text, what about using three columns?

LHS of "±" in first column, right justified
"±" in second column, centred
RHS of "±" in third column, left justified
first & third columns same width

can format so that centre cell borders don't show

DanteAmor · May 10, 2019

Try this macro.

Change the letter "C" in red to the letter of the column where you have the texts.
The font of the texts must be one in which each letter has the same dimensions. I gave the example of the "Consoles" font

Code:

Sub Align_Text()
    Dim wMax As Long, i As Long, n As Long, col As String, j As Long
    
    col = "[B][COLOR=#ff0000]C[/COLOR][/B]"   'column with text
    Range(col & ":" & col).Font.Name = "[B][COLOR=#ff0000]Consolas[/COLOR][/B]"
    wMax = 0
    For i = 2 To Range(col & Rows.Count).End(xlUp).Row
        n = InStr(1, Cells(i, col).Value, "±")
        If n > wMax Then
            wMax = n
        End If
    Next
    For i = 2 To Range(col & Rows.Count).End(xlUp).Row
        n = InStr(1, Cells(i, col), "±")
        If n < wMax Then Cells(i, col).Value = Space(wMax - n) & Cells(i, col).Value
        For j = Len(Cells(i, col).Value) To 1 Step -1
            If IsNumeric(Mid(Cells(i, col).Value, j, 1)) Then
                Exit For
            Else
                Cells(i, col).Characters(Start:=j, Length:=1).Font.Superscript = True
            End If
        Next
    Next
End Sub

Result:

DanteAmor · May 10, 2019

A small change in the macro, only to eliminate a "For" cycle, but both work the same

Code:

Sub Align_Text()
    Dim wMax As Long, i As Long, n As Long, col As String, j As Long, lr As Long
    
    col = "C"   'column with text
    lr = Range(col & Rows.Count).End(xlUp).Row
    Range(col & ":" & col).Font.Name = "Consolas"
    wMax = Evaluate("=MAX((FIND(""±""," & col & "2:" & col & lr & ")))")
    For i = 2 To lr
        n = InStr(1, Cells(i, col), "±")
        If n < wMax Then Cells(i, col).Value = Space(wMax - n) & Cells(i, col).Value
        For j = Len(Cells(i, col).Value) To 1 Step -1
            If IsNumeric(Mid(Cells(i, col).Value, j, 1)) Then
                Exit For
            Else
                Cells(i, col).Characters(Start:=j, Length:=1).Font.Superscript = True
            End If
        Next
    Next
End Sub

Align Text on a Specific Character

SledLax

New Member

Excel Facts

DanteAmor

Well-known Member

SledLax

New Member

DanteAmor

Well-known Member

shg

MrExcel MVP

SledLax

New Member

SledLax

New Member

Fazza

MrExcel MVP

DanteAmor

Well-known Member

DanteAmor

Well-known Member

Similar threads

Forum statistics

Share this page

Align Text on a Specific Character

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

MrExcel MVP

New Member

New Member

MrExcel MVP

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock