Maximum number of charecters allowed

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
278
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
 

Some videos you may like

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
Joined
Oct 12, 2006
Messages
44,061
You can use the LEFT function..

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

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
278
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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Nov 18, 2005
Messages
278

ADVERTISEMENT

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
Joined
Oct 12, 2006
Messages
44,061
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)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,309
Office Version
  1. 2010
Platform
  1. Windows
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.).
 

Watch MrExcel Video

Forum statistics

Threads
1,108,985
Messages
5,526,063
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top