Using "Right" condition is not helping me....Pls

**munna316** · 10-22-2013, 09:33 AM

Hi,

Anybody Please help me on the below...

I have the following

Cell A has "ABCDEF0123456789", i used "Right" condition to get the number from the cell, but I want to remove the number instead of copying the same.

I want only ABCDEF left in Cell A.

Please help,

Thanks in advance

**Fotis1991** · 10-22-2013, 09:44 AM

If your text is always in the leFt site of the string use this array FORMULA.

Formula:

Please Login or Register  to view this content.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

**Jonmo1** · 10-22-2013, 09:45 AM

Try using the LEFT function to "get" the ABCDEF.
Then copy that formula cell
And use PasteSpecial/Values back on top of the original cell.

Or if you know it's always the left 6 characters you want,
Try using Data - Text to columns - Fixed width - next
Set the width to the 6th character - next
Highlight the 2nd column and choose "Do not import this column"
Finish

**Moo the Dog** · 10-22-2013, 09:47 AM

Well, if you want replace the contents of cell A1 with just the first 6 letters of what is in A1, you have multiple options (not all listed):

1) Use a macro to replace all characters to the right of the letters with ""

or

2) Assuming your data starts in cell A1, use this formula in B1 and fill down: =LEFT(A1,6)

You can then copy B1:Bxxx and using Paste Special: Values over the contents of column A.

Of course, if your data has varying amounts of letters before numbers, then you will have to use a different formula, one that searches for the first number in each cell and only copies the first letters to the new cell. Something like:

Formula:

Please Login or Register  to view this content.

- Moo

**munna316** · 10-22-2013, 10:45 AM

Thank you for your replies...
But I still did not get my answer

aaaaa123456467
dsfadf213146
4545646fad
asdfsdf54566664
ewrwer4564789

how can I extract only numbers from the above? or
how can I extract only text from the above?
I tried text to columns but couldnt find my answer

please help

thanks in advance

**Jonmo1** · 10-22-2013, 10:51 AM

With the additional info (that probably should have been included in the original post)..

It appears it's NOT always the left 6 characters.

It becomes a much more difficult task to extract only the numbers or only the text..
Particularly if there is no real pattern, sometimes the letters are first, sometimes the numbers are first..
And it's not always the same # of numbers or letters.

Can you post an actual book with a realistic sample set of data and your desired results?

**Fotis1991** · 10-22-2013, 11:01 AM

Formula:

Please Login or Register  to view this content.

And this ARRAY formula.

Formula:

Please Login or Register  to view this content.

Replace all semi colons in my formula to comma!!

**Ghozi Alkatiri** · 10-22-2013, 11:39 AM

array formula
to extract number
=MID(A1,MIN(IFERROR(IF(--MID(A1,ROW($1:$100),1),ROW($1:$100)),"")),COUNT(--MID(A1,ROW($1:$100),1)))

to extract text
=SUBSTITUTE(A1,MID(A1,MIN(IFERROR(IF(--MID(A1,ROW($1:$100),1),ROW($1:$100)),"")),COUNT(--MID(A1,ROW($1:$100),1))),)

Using "Right" condition is not helping me....Pls

LinkBack

Thread Tools

Rate This Thread

Display