# Maximum number of charecters allowed

#### Swaroon

##### Active Member
Hi,
Is it possible to add a piece of code to the formula below to limit the answer eg

Sheets("data").Range("BN" & x + 1).Select
xCustomerShort = ActiveCell.Value

Returns eg:
ActiveCell.Value = "Stephen"

Required :
ActiveCell.Value Limit to 4 = "Step"

thanks
Steve

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Jonmo1

##### MrExcel MVP
You can use the LEFT function..

ActiveCell.Value = Left("Stephen",4)

#### Swaroon

##### Active Member
works great, thanks

Is there any way of making the last piece of code a flexible value - pls see below ?

Sheets("data").Range("A" & x + 1).Select
xAgreement = Left(ActiveCell.Value, 10)

Sheets("data").Range("BN" & x + 1).Select
xCustomerLong = Left(ActiveCell.Value, 60)

Sheets("data").Range("BN" & x + 1).Select
xCustomerShort = Left(ActiveCell.Value, xx) total charecters available = 100 so xx needs to max out at 30 (10+60+30 = 100)

#### Jonmo1

##### MrExcel MVP
Not sure I understand...

Where did the 100 come from?
Is that the Length of ActiveCell.Value ?

You could try
xCustomerShort = Left(ActiveCell.Value, Len(ActiveCell.Value)-60-10)

#### Swaroon

##### Active Member

The code determines the name a file will be saved with - concatenating various data pieces - I have a max of 100 charecters i can use, if I use 10 + 60 = 70 then I have a max of 30 available, if i use 10 + 50 then I have 80 - make sense ? The length of each value will change, max being 10 and 60 but if less used, would want to use on final concat

#### Jonmo1

##### MrExcel MVP
The length of each value will change
HOW exactly ?
The code you posted has the 10 and 60 hard coded, so the simple solution is to also hard code the 30.

do you have some variables holding the 10 and 60 values?
X = 10
Y = 60

Then in the final line that uses the remainder from 100
Z = 100 - X - Y

xCustomerShort = Left(ActiveCell.Value, Z)

#### Swaroon

##### Active Member
Thanks for the help and advise, I'll check it out

cheers
Steve

#### Rick Rothstein

##### MrExcel MVP
HOW exactly ?
The code you posted has the 10 and 60 hard coded, so the simple solution is to also hard code the 30.

do you have some variables holding the 10 and 60 values?
X = 10
Y = 60

Then in the final line that uses the remainder from 100
Z = 100 - X - Y

xCustomerShort = Left(ActiveCell.Value, Z)
Don't forget to subtract the total length of any delimiters between X & Y and between Y & xCustomerShort also (2 if there is a space between them, 4 if there is a comma/space between them, etc.).

Replies
1
Views
24
Replies
12
Views
119
Replies
22
Views
670
Replies
3
Views
81
Replies
5
Views
78