# Thread: Number of Characters (Excel 2002)

1. ## Number of Characters (Excel 2002)

Hello!
I'm needing to chop off or add spaces to data within a cell so that it is only 40 characters long.
I've used the following formula in cell A2
=LEFT(A1,40)
Some of the data is LESS than 40 characters, and some of the data is MORE than 40 characters. It has to be exactly 40 characters in that cell.
In cell A3 I've added the following formula
=LEN(A2)
This counts how many characters.
In cell A4 I have another formula
=A2&REPT(" ",40-LEN(A2))
This then adds spaces to those that are less than 40 characters.

Is there an easier, more direct formula for this?
Thanks!
Lana

2. ## Re: Number of Characters (Excel 2002)

In one formula:
<code>
=LEFT(A1&REPT(" ",40),40)
</code>
The formula tacks 40 spaces at the end of the value of A1, then takes the first 40 characters of the result.

3. ## Re: Number of Characters (Excel 2002)

Hello!
I can't get the REPT part of the code below to work... when I leave the REPT part off and just have the LEFT part of the formula it works great... I obviously have something written wrong????
Thanks!
Lana

Sub LengthofData()

Dim lngRow As Long

lngRow = 2
Do While Range("A" & lngRow) <> ""
Range("V" & lngRow) = Left(Range("M" & lngRow) & Rept(" ", 40), 40)

lngRow = lngRow + 1
Loop

End Sub

4. ## Re: Number of Characters (Excel 2002)

Try

Application.WorksheetFunction.Rept(" ", 40), 40)

Rept(" ", 40), 40)

5. ## Re: Number of Characters (Excel 2002)

Hi There I thought I would just add this refined code to help adjust your figures if you have a variable length:

Sub LengthofData()

Dim lngRow As Long
Dim i As Integer

lngRow = Range("A65536").End(xlUp).Row

For i = lngRow To 2 Step -1
Range("V" & i) = Left(Range("M" & i) & Application.WorksheetFunction.Rept(" ", 40), 40)

Next i

End Sub

Enjoy

6. ## Re: Number of Characters (Excel 2002)

REPT is a worksheet function. The VBA equivalent is String. Instead of REPT(" ",40) use:

<code>
String(40," ")
</code>

7. ## Re: Number of Characters (Excel 2002)

Thanks Legare... this worked perfect!
Lana

8. ## Re: Number of Characters (Excel 2002)

Lana

Just a small point, I believe my answer works with the addition of code...sometimes a little recognition helps, I needn't have done it for you and a bit of politeness goes a long way...

9. ## Re: Number of Characters (Excel 2002)

Hi Jerry,
I apologize for my oversight on not giving credit to all that replied to my request for help! It was not my intention to offend anyone, nor to present myself as being ungrateful or rude... in fact these are pet peaves of mine, and I truly can understand your feelings. I am always very appreciative of any and all help I recieve... this includes the help that you provided! Please accept my apologies!
Lana

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•