Centre Text in Cell around "-"

[MGumbrell]

I have a number of cells that contains various lengths of text either side of a - within that cell.

What I would like to happen

Can I set the centre text justification so that the "-" is treated as the centre of each cell i.e

So that this
gegew - uudh
ee-h
dgdvgfdgg-d
gsv-bgvdvcvdcdgsvcg

Becomes this
gegew - uudh
ee - h
dgdvgfdgg - d
gsv - bgvdvcvdcdgsvcg

well I guess you understand what I would like to acheive.

Regards, Matt

 

[HaHoBe]

Hi, Matt,

the Replace-function does alright for me when calling it with Ctrl+H.

If you need the function in a cell to do so use

=REPLACE(A1,FIND("-",A1,1),1," - ")

Option Explicit

Sub MattUsedRange()
Dim rngCell As Range
Const strSearch As String = "-"
Const strNew As String = " - "

Application.ScreenUpdating = False
For Each rngCell In ActiveSheet.UsedRange
  If Not IsEmpty(rngCell.Value) And InStr(1, rngCell.Value, strSearch) > 0 Then
    rngCell.Replace what:=strSearch, Replacement:=strNew, lookat:=xlPart, _
        searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  End If
Next rngCell
Application.ScreenUpdating = True
End Sub

Sub MattSpecialCells()
Dim rngCell As Range
Const strSearch As String = "-"
Const strNew As String = " - "

Application.ScreenUpdating = False
For Each rngCell In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, 2)
  If InStr(1, rngCell.Value, strSearch) > 0 Then
    rngCell.Replace what:=strSearch, Replacement:=strNew, lookat:=xlPart, _
        searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  End If
Next rngCell
Application.ScreenUpdating = True
End Sub

Ciao,
Holger

 

[MGumbrell]

Sorry Holger

I didn't explain what I was after very clearly.

The result I am looking for is that the text in each cell is centered around the "-".

I have attached a file that I hope explains better than words. I would like the contents of the cell to align after the cell is changed.

Regards, Matt

 

[MGumbrell]

It didn't let me upload the file, so I will have a go again.

Matt

 

[Rod D]

Macro solution

Hi Matt

This macro will add spaces to either end of the cell contents to balance the number of characters either side of the "-" mark - effectively centering the "-" sign in every cell selected before the macro is run.

However, you will have to us a non-proportional (TruType) font - i.e. Courier - for the cells to centre exactly.

HTH
Rod

Sub CentreOn()
'
' CentreOn Macro
'
Dim Length, Position As Integer
Dim rngCell As Range

Application.ScreenUpdating = False
For Each rngCell In Selection
  Length = Len(rngCell.Value)
  Position = InStr(rngCell.Value, "-")
  If Position <= Length / 2 Then
    rngCell.Value = String((Length - Position) - (Position - 1), " ") & rngCell.Value
  Else
    rngCell.Value = rngCell.Value & String((Position - 1) - (Length - Position), " ")
  End If
Next rngCell
Application.ScreenUpdating = True

End Sub

 

[HaHoBe]

Hi, Matt,

just a tip - maybe better use the Code-Tags next time to make the spaces visible (as HTML is not supported in this forum - so   will not be of any use):

gegew-uudh 
ee-h 
dgdvgfdgg-d        
            gsv-bgvdvcvdcdgsvcg
            gsv-bgvdvcvdcdgsvcg
            gsv-bgvdvcvdcdgsvcg

Ciao,
Holger

 

[wiklendt]

Re: Macro solution

Hi Matt

This macro will add spaces to either end of the cell contents to balance the number of characters either side of the "-" mark - effectively centering the "-" sign in every cell selected before the macro is run.

However, you will have to us a non-proportional (TruType) font - i.e. Courier - for the cells to centre exactly.

HTH
Rod

Sub CentreOn()
'
' CentreOn Macro
'
Dim Length, Position As Integer
Dim rngCell As Range

Application.ScreenUpdating = False
For Each rngCell In Selection
  Length = Len(rngCell.Value)
  Position = InStr(rngCell.Value, "-")
  If Position <= Length / 2 Then
    rngCell.Value = String((Length - Position) - (Position - 1), " ") & rngCell.Value
  Else
    rngCell.Value = rngCell.Value & String((Position - 1) - (Length - Position), " ")
  End If
Next rngCell
Application.ScreenUpdating = True

End Sub

i find it extremely funny that this thread was originally posted in 2006, yet M$ still, in 2009, has not made this feature (align by arbitrary character) available as an out-of-the-box standard!

anyway - when people say "macro" re: excel, do they mean VBA in the VBA editor? just that this looks nothing like what i know macros to look like, but look damn near close to VBA...

 

[HaHoBe]

Hi, wiklendt,

AFAIR Excel is a spreadsheet, not a word processor I like it if I can line up the numbers neatly, dan that´s good enough for me (I´ll use Word if I want more)..

The difference between macro and VBA in excel isn´t that strict as in Access because you got the possiblility to record a macro which isn´t preset like in Access. Both a recorded macro as well as a procedure from scratch may go under the more common name of macro in Excel.

Ciao,
Holger