Text Formatting

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I need some help ...

I have a column of text that must always maintain a set # of characters (40) regardless of how short the text value is.
Is there a way to force spaces to be added ... ie formatting a cell that ensures there are always 40 characyers? Something like entering a number 23.45 into a cell formated as 000.0000 gives you 023.4500?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
Hi there RobK, welcome to the Board,

Paste this formula in B1 and scroll down:

=A1&REPT(" ",40-LEN(A1))

This will add spaces to anything in column A so that the total number of characters equals 40

Hope this gives you some ideas

regards
Derek
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Robk:

Just to add to Derek's contribution, you may add the space character (or for that matter any other character), before or after the actual word to make it of any specific length (in our case here that is 40).
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This is fantastic!!! Thanks for your help everyone.
Yogi .... how would I place spaces in front of the text, rather than after?
 

brucesw

Active Member
Joined
Nov 12, 2002
Messages
301
Not to steal anyone's thunder but swap the formula around.

=REPT(...)&A1

The & is a shortcut for concatenate. It joins two text strings together.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top