I’ve been hitting the books and help function for an hour - this should be easy, but I cannot get anything to work
In Excel for Windows 95:
I have a list of accounts similar to the following:
050.00 - Wells Fargo Widget Account
100.10 - Wells Fargo Embezzlement Account
750.20 - Salaries for Useless Dweebs
etc., for about 800 lines. I need to somehow isolate and delete the first 9 characters in the string, so my list will look like this:
Wells Fargo Widget Account
Wells Fargeo Embezzlement Account
Salaries for Useless Dweebs
I know this is probably absurdly simple, but I can’t get it to work. Can anyone help me?
Mid(text, start, num_chars)
Text is the field you are trimming
Start would be 10 (the first character of the text you want)
Num_Chars would be Len(field) - 9
When I am having trouble finding a function, I bring up the function list (Select More Functions … from the drop down to the left of the field editing area) and look at the lists. There is a short description that goes with the function that helps, and sometimes the name is a giveaway.
I don’t know if it can be done in the spreadsheet; however, it’s easy to do in VBA, the programming language that comes with Excel. Record a macro that selects a cell and edits it, then go into the editor (can’t remember the key off the top of my head). The function to return a suffix of a string (which is what you want to do) is right$. Look that up in the help.
Thanks you guys. I’ll see if I can make either of those work for me.
I used to work in a place where there was an Excel guru handy so I got lazy about all this. Now I’m trying to recover my lost knowledge and add to it, but it’s slow going.
I don’t know which version of Excel you’re using, but (IIRC) all versions since Excel 97 have a menu command to split data into columns:
Highlight the column with the text you want to separate
Data > Text to Columns
Click on “Delimited”, click Next
Choose “Other” as your delimiter and type in the “-”
Click Next, next,…until you’re done.
Earthling, I’ll see your text->columns/delimited and raise you text->columns/fixed length. The same function you use with a delimiter can be used with fixed length columns.
Thank you for pointing that out. Somehow I got it into my head when I posted that “fixed length” means all columns are of the same width & therefore doesn’t apply to the OP - but of course that’s not the case. And I knew that, too. What was I thinking. :o