How do I exclude text???

[lsf]

Can't find an answer anywhere, thanks in advance. I have a column of
cells containing text and numbers within the same cell, i.e.,
"1@132525" (only the data contained within the the quotation marks).
I would like to perform operations on only the six digits to the right
of the "@"--would appreciate any help! Thanks!

 

[joeu2004]

I have a column of
cells containing text and numbers within the same cell,
i.e., "1@132525" (only the data contained within the the
quotation marks). I would like to perform operations on
only the six digits to the right of the "@"

If there are always 6 digits, then use RIGHT(A1,6).

If the number of digits is variable but always after the first 2
characters, then use MID(A1,3,99). The "99" is arbitrary.

If the number of digits and the length of the prefix is variable, but
the digits always follow "@", then use MID(A1,FIND("@",A1)+1,99).

In some contexts, you might need to convert the extract numerical text
to a number explicitly, e.g. --RIGHT(A1,6).

That should be needed only with RIGHT() or MID() is not used in an
arithmetic expression, e.g. IF(--RIGHT(A1,6) > 100000,...).

 

[Rob Golden]

There are a couple of functions that could help you in situations like this, most of which involve the finding and replacing of values stored in cells. For your specific question the simplest thing would be to use a combination of =VALUE and =REPLACE. Example:

If cell A1 contains the "text string":
example@25
And cell A2 contains the "text string":
example@15
Then in cell B1 and B2 you could use:
=REPLACE(A1, 1, 8, "") in B1 and
=REPLACE(B2, 1, 8, "") in B2
What this does is takes the cell in the first argument (A1 or A2, respectively), starts at Character 1, goes 8 characters in, and replaces those 8 characters with "" which is nothing.
=REPLACE(Cell to look at, character to start at, number of characters to replace, what to replace it with).
This will, however, return the "text version" of the numbers, 25 and 15, and you will not be able to add them. SO, use the =VALUE function which returns the number value of a text string that is actually a number. You can use them together like this:
=VALUE(REPLACE(A1, 1, 8, ""))

This will return the number value of the text that the replace function returns, which will be a number. You will have to play with the number of characters in the REPLACE function to work with whatever your cells contain.

If that doesnt work for you post an example of what the cells look like?

Good luck,
Rob

Submitted via EggHeadCafe
Win a 2 Year Personal Class Hosting Account From Arvixe.com
http://www.eggheadcafe.com/tutorial...nal-class-hosting-account-from-arvixecom.aspx