Need help on an Excel formula!

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?

Wow, two Excel questions in row for me. :slight_smile:

The function you want is Mid combined with Len.

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.

Good luck

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.

No need to use LEN. =MID(RC[-1],9,99) will strip the first 8 chars and leave the rest untouched, just use a number longer than the longest string.

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 got eepunk’s working before I saw yours, sailor. Now I have several solutions. Thanks to you all.

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