How to remove #'s 0-9 and spaces

Hmm?
As a matter of interest.
Your original nested SUBSTITUTE function seems okay in 2007 (provided you get rid of that extra space).,,,,,,SUBS TITUTE........

There is a limit of 7 nested functions in pre-2007 versions, what version are you using?
Your profile indicates 2010, I would have thought it should handle the formula.

Originally Posted by Marcol

Hmm?
As a matter of interest.
Your original nested SUBSTITUTE function seems okay in 2007 (provided you get rid of that extra space).,,,,,,SUBS TITUTE........

There is a limit of 7 nested functions in pre-2007 versions, what version are you using?
Your profile indicates 2010, I would have thought it should handle the formula.

I am using Excel 2010, but i still get the error that i have reached the allowed levels of nesting....

I just want to thank all of you guys for assisting with this, i really do appreciate it a lot. I wish I knew excel and all theses formulas, this is extremely convenient lol I know a bit of web design and thought that was hard haha, this excel / vbs is no joke =P

I will go ahead and try Pike's suggestion when I get home tonight, so Pike, Thank you for your input on this my previous post you were very helpful as well.

martindwilson: I shall look into ASAP, I would just rather do everything within excel in order for me to learn a bit here and there as well, I like learning new things

Charlie_Howell: I checked out the formula and it is like a foreign language to me lol. Its amazing that a formula can do all that, but im confused with it, so when i get home tonight I shall try to dissect it a bit further to see if I understand how it works and see if I am able to implement it a bit more into my excel sheet.

I basically have a list of a few thousand users, and many people enter their First or Last name with numbers, due to the fact of how my system is designed, they were advised to use a number that corresponds with their other ID in the system to get the same ID instead of the system generating something else. (complicated explanation), but Basically can have 1ScoTT or Sc0tt or ScoTT1, basically anything else that is why i need to just convert it to PROPER and remove any numbers that they can have 0-9 along with any spaces their might be.

that cool formula is conected to Pikes Brilliant code..."User Defined Function", all I did was ad some simple fuctions along with it...

about the issue with it not nesting in 2010, its because it was a .XLS file instead of a .XLSX file, because I used 2010, but the spreadsheet that I download on a weekly basis, comes in XLS, so that is probably why, but I shall try everyones suggestions later tonight guys

One more question if you guys dont mind....

Please Login or Register  to view this content.

This formula adds an #N/A if there is nothing in the other worksheet to compare to, is there anyway to change the #N/A to display nothing.... meaning for all the things that it doesnt find it just leaves a blank cell instead of saying that it is basically (not applicable).

Please let me know.
Thanks in advance

@ elite-fusion (OP)
Only a wild guess.... is your version of 2010 up to date with service packs?
Have you downloaded the latest updates?

There are probably more elegant solutions to your problem, but I am interested as to why that basic formula should not work.

Originally Posted by Marcol

@ elite-fusion (OP)
Only a wild guess.... is your version of 2010 up to date with service packs?
Have you downloaded the latest updates?

There are probably more elegant solutions to your problem, but I am interested as to why that basic formula should not work.

it wasnt working because it was done on a .XLS which is a 97-03 version excel file, but it works fine in .XLSX which is a 07-10 version excel file.

Originally Posted by martindwilson

=IF(A2=A2,iferror(INDEX(Training!H:H,MATCH(A2,Training!C:C,0),1),""),"")

this worked like a charm....is there anyway to run this command automatically for several different columns, meaning for one column it looks and up and matches one thing from Training worksheet, and another column will do the same but lookup other columns from the Training worksheet...so i do not have to do fill down each cell manually, and plus the Training worksheet is always different, one week can be 1000 rows, and another time it can be 2500 rows, the amount of columns always stays the same...

Basically is there anyway to use the formula above as a "user defined function" or whatever it is called, basically something like PIKE suggested to use by using the View Code in excel for a worksheet?

Please advise

once again, thank you for everyones help in this.

also guys...

=IF(D2=D2,IFERROR(CONCATENATE("http://example.com/sps/d4kh12az-c.php?wl=",D2),""),"")

I want it so that if D2 has any value in it, for it to produce the concatenated formula, but if the field is blank, for it not to display the link at all...because the formula above gives me

"http://example.com/sps/d4kh12az-c.php?wl=" if D2 is blank, but i want it do leave the cell blank with no URL, and only make the URL if D2 has a value in it

D2: "J-DOE123"
URL: "http://example.com/sps/d4kh12az-c.php?wl=J-DOE123"

but if...

D2: ""
URL: ""

Please assist, because all i was able to figure out that if i change in the formula above "=IF(D2=D2," to "=IF(D2="J-JOE123," but that would ONLY look up if the D2 is J-DOE123 then it will populate, otherwise it will leave it blank, WHICH IS WHAT I NEED, BUT FOR A WILDCARD SCENARIO, NOT "J-DOE123", BUT INSTEAD FOR ANY VALUE.

well i used this formula in several different columns, so one of them is A2 and one is D2, but any idea how I can make it so that it concatinates and leaves it blank if the D2 field is blank?

and also, is there anyway to run this as a UDF as my previous post?

I think you can simply highlight the column and then click 'find' and fill in what you want to find, then click 'replace' - but leave replace blank and it will replace with a blank. If you want to replace with a space then put a space in the 'replace' area. Can you give an example?