Removing unwanted characters

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

[pinmaster]

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

"jermsalerms" wrote:

>
> 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.
>
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=503024
>
>

[jermsalerms]

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.

[jermsalerms]

Better but is there anything that can be done with out the control shift operation....
also I jsut tried this and in B1 I get a result #NAME?

[Ron Coderre]

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

[Ron Coderre]

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


"jermsalerms" wrote:

>
> 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.
>
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=503024
>
>

[CLR]

The free Excel Add-in called ASAP Utilities, available at
www.asap-utilities.com has standard features that will do this for you quite
nicely......

Vaya con Dios,
Chuck, CABGx3



"jermsalerms" wrote:

>
> 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.
>
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=503024
>
>

[jermsalerms]

Any idea how to use it to do this?

[jermsalerms]

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.

[Ron Coderre]

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,
Ron

[jermsalerms]

Thank you very much for your help...that last one is exactly what I was looking for

[SteveG]

Another option.

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

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


Does that help?

Steve

[SteveG]

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