Forum Home
Press F1
Thread ID: 33618	2003-05-21 03:20:00	Excel - Limiting amount of text in a field	Kame (312)	Press F1

Post ID	Timestamp	Content	User
146106	2003-05-21 03:20:00	Is there a way to do this. I am rewriting an exported text file from a fax program and one of the fields will only allow a max of 45 characters, so is there a way to make one column of excel to limit the characters to 45. As I don't know what the results would be like if I import it back and the characters are over 45.	Kame (312)
146107	2003-05-21 03:39:00	=left(A2,45) will reduce the text in a2 to 45 characters. That won't stop it on input, but will trim it for you. robo.	robo (205)
146108	2003-05-21 03:43:00	You could buy robo's book. :D	Graham L (2)
146109	2003-05-21 03:45:00	Actually, I don't think it's in there. (checks). Well, it is briefly, said more in my last posting. robo.	robo (205)
146110	2003-05-21 03:48:00	Make notes for the new edition. :D	Graham L (2)
146111	2003-05-21 03:55:00	Cheers Robo, So that will truncate the text if it's over 45 characters? This is so far the best option I have, I was dreaming for something that would like beep in my ears saying I'm crossing the line or something. Like the annoying error wave file that beeps when you run out of room. As this would make editing the text straight away than leaving it all till last. I have over 144 contacts to enter and 144 contacts is just for all the 'A' listings.	Kame (312)
146112	2003-05-21 04:17:00	I could buy his book, and then go through it, and then moan and complain why this or that never worked on my Office Excel E-Gads 2006 Version. Not much changes but new features added are never in the book. I still got my Lotus 1-2-3 book from the early 90s sitting around and it might have a similar function but that would take me longer to read and find it considering I have no idea what function I'd be looking for exactly. I don't even know what the function robo said is called. To me it's like a truncator... so that's possibly where I'd start.	Kame (312)
146113	2003-05-21 21:53:00	It's basic string manipulation and the command has been in fortran, cobol, basic, pascal, just about every language since the dawn of time. A trick would be to do something like this in the cell to the right of the data entry: =if(len(a2)>45,"settle down, big boy","") You can put any text you like in the first set of speech marks "!" as a warning for instance to keep the column narrow. Not sure about beeps, but it will appear as soon as you hit ENTER. robo.	robo (205)
146114	2003-05-22 01:38:00	ahh you are speaking my language now, didn't realise I could use len() in Excel, wow, maybe spreadsheets do have more purpose than making my data entry life easier.	Kame (312)
146115	2003-05-22 01:56:00	Well this works great, although 45 characters is a lot and I realised that so far none of the entries I've put in is 45 characters long :/, looks like I'm going to buy your book anyway as it may help with my programming, any discount for saying you are a PressF1'er VIP uber leet kiddo with Computomathematically Modified Problems and Answers? Nah I guess they wouldn't understand me, guess it's double price again. I may look into the beep side of things robo, now that I understand that you can use some type of programming language (I like programming), I wonder if there's a way to play a wav file in that if statement. If you can make BASIC use the internal speaker to play funky tunes then you can maybe, just maybe be able to play your favourite wma/mp3 while working on Excel. In fact if Excel can run custom made scripts then it will do what I want it to do, even a prompt box with a message saying I've crossed the line.	Kame (312)
1 2