How to remove #'s 0-9 and spaces

[elite-fusion]

Hello guys,

Have a spreadsheet, want to automatically do a PROPER command and a SUBSTITUTE command in order to make it look "proper" first and then remove all numbers 0 to 9 and also all spaces.

so far I have

=PROPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"0","")," ",""))

But it does not work, Excel says too many levels of nesting.

Any idea how i can automatically PROPER and remove all numbers and spaces? (and for future reference any particular characters besides #'s or other characters)

[pike]

Hi elite-fusion
you would have to break the function up over two cells

or use a User defined function

Please Login or Register  to view this content.

[pike]

looking at the udf there is no need for the c loop

Please Login or Register  to view this content.

[martindwilson]

try asap add in there is a text option advanced character removal that will let you select any chars you want to remove http://www.asap-utilities.com/

[Charlie_Howell]

Try this in conjunction with Pikes Code =PROPER(RemoveNumeric(SUBSTITUTE(A1," ","")))

[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.

[Charlie_Howell]

yes! you are correct....
=PROPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9", ""),"0","")," ",""))
seems to work perfectly

updatewhen I post this fomula it adds a spce in substitute...lol IDK

[elite-fusion]

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.

[Charlie_Howell]

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

[martindwilson]

tried nesting ok in 2007 so should work in 2010

[elite-fusion]

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

[martindwilson]

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

[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.

[elite-fusion]

@ 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.

[elite-fusion]

=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.

[elite-fusion]

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.

[pike]

@ Charlie_Howell
Wouldn't get to carried away as its a variation from the attachment

any way martin solved the problem

@ elite-fusion
to keep with-in the title can you please mark this thread solved and start a new thread with the new question

[martindwilson]

oops i noticed a2=a2 and d2=d2 bits they need resolving

[elite-fusion]

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?

[LMinGA]

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?