How to add spaces to the both sides of text in a cell

[sami9]

I need to add spaces in the beginning and end of a text. e.g Text is John. I need 4 spaces before J and 7 spaces after n. How to accomplish this.

 

[gaz_chops]

Is it always 4 spaces at the beginning & 7 spaces at the end?

 

[mohadin]

Hi what about

=REPT(" ";4)&G1&REPT(" ";7)

 

[Peter_SSs]

It isn't clear ..
- where you want the results, or
- whether you are looking for a formula or vba solution

For putting the results in another column,

another formula suggestion is
=REPLACE(REPT(" ",11),5,0,A1)
or perhaps if there might be blank cells you might want
=IF(A1="","",REPLACE(REPT(" ",11),5,0,A1))

a macro suggestion is

Sub AddSpaces()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("if(row(#),replace(rept("" "",11),5,0,#),"""")", "#", .Address))
  End With
End Sub

or again if blanks you might want

Sub AddSpaces()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("if(row(#),if(#="""","""",replace(rept("" "",11),5,0,#)),"""")", "#", .Address))
  End With
End Sub

 

[sami9]

I am Pasting this in the new cell but result is not coming as the formula is not showing the cell selected i.e H5. I have changed the G1 to H5 as my data is in G5. in response to mohadin

 

[mohadin]

if you data in G5 and the result in H5 then

H5=[COLOR=#333333]REPT(" ";4)&G5&REPT(" ";7)[/COLOR]

 

[sami9]

Thanks.

=REPLACE(REPT(" ",11),5,0,A1)

This one worked for me. I needed formula and it served my purpose

 

[Rick Rothstein]

Sub AddSpaces()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("if(row(#),replace(rept("" "",11),5,0,#),"""")", "#", .Address))
  End With
End Sub

You could simplify the line of code highlighted in red slightly by doing it this way...

Sub xAddSpaces()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate("if({1},replace(rept("" "",11),5,0," & .Address & "),"""")")
  End With
End Sub

 

[sami9]

Sorry my data was in H5. But I tried the above by keeping data in G5 and formula is H5 but still it is not working. ref to mohadin

 

[mohadin]

Then what will happen if "Johanson" e,g