Removing unwanted characters

  • Thread starter Thread starter jermsalerms
  • Start date Start date
J

jermsalerms

I am trying to come up with a formula that will remove any character
that is not a number (0,1,2,3,4,5,6,7,8,9)

I have cells that contain the number of years of employment and want to
standardize it so it is all the same.

This is an example of what I have now:

A1 - 10
A2 - 5yrs
A3 - 1Y
A4 - 15YRS
A5 - 12Yr

I want to make all the cells appear like A1...just the numbers

I am thinking something along the lines of "remove character if it is
not 0, 1, 2, 3, 4, 5, 6, 7, 8, or 9" but I am unsure who to write that.
 
You can use the Find and Replace feature, start by selecting your data then
go to Edit/Replace.....in the Find What box type: yrs then click on Replace,
leave the Replace with box empty and hit Replace All, repeat for any
remaining character.

HTH
JG
 
Try this:
For some value in A1 that starts with numbers and ends with text.

B1:
=LEFT(A1,MAX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))*ROW(INDIRECT("1:"&(LEN(A1))))))

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

Example:
A1: 12.5 years
The above formula returns 12.5

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
This is a list of 900+ records. I am trying to make it more automated
than having to figure out all the variables I have to remove.
 
First: You might be getting the #NAME! error because sometimes the
ExcelTip forum adds extra spaces. There should be NO spaces in the
formula I posted:

B1:
=LEFT(A1,MAX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))*ROW(INDIR
ECT("1:"&(LEN(A1))))))

Note: Commit that array formula by holding down the [Ctrl][Shift] keys
and
press [Enter].

Second, after entering that formula, you can just copy it down as many
rows as you need. That doesn't seem too inconvenient, is it?

Regards,
Ron
 
I looked at ASAP but I must manually select what I want to convert.
This is a spreadsheet that auto updates every day with new data and
exports to a CRM system daily all on its own. having to go in and
manually change things would not be an option.
 
Another option.

If all your text strings begin with "y" or "Y" then you could tr
this.

Select your range, go to Data>Text to Columns. Choose Delimited,clic
on Next. Uncheck all options but Other and type a lower case y in th
box to the right of the option. Click Next and Finish. Repeat thi
but using the upper case Y this time.


Does that help?

Stev
 
See if this works for your specific situation:

First, here are the rules:
-Since the numbers represent years, they will be between 0 and 99
-Values with begin with numbers
-They may or may not end with text
-There will be no decimals or punction in the numbers

If those rules apply, then:
B1: =IF(ISERROR(--LEFT(A1,2)),--LEFT(A1,1),--LEFT(A1,2))

Copy that NON-array formula down as far as you need it

Does that help?

Regards,
Ro
 
A slight modification to Ron's last post.

=IF(ISBLANK(A1),"",IF(ISERROR(--LEFT(A1,2)),--LEFT(A1,1),--LEFT(A1,2)))

This way you can drag this down the entire sheet if you want and as new
data is entered in column A, it will appear in whatever column you are
using this formula in otherwise it will remain blank. This just
eliminates the #VALUE! error if you drag this down where no data is in
A yet.

Steve
 
I'm glad that worked for you
(although, I winced when I saw how many typos were in my post!)

Regards,
Ron
 
Back
Top