Copy frist six characters of a string only if first character is non numeric in cell

**DeeBee** · 06-21-2012, 10:54 AM

hi guys,

please help me? i've tried left and mid and trim but can't get to do the following;

cell B1 "abc123 1 2 3 adc"
Cell B2 "123adc123"
Cell B3 "1 b c 2 d e 123abc"

i need a fromula that will copy first 6 characters in cells in column b to new column only if as in B1. ie. first character is non numeric and first 6 characters are not seperated.

Please help me?

thx
DeeBee

**weeble33** · 06-21-2012, 11:06 AM

Hi DeeBee,

I almost have what you're looking for:

Please Login or Register  to view this content.

But for some reason, the ISNUMBER() returns false regardless if the first character is a number....I'll look into it a little more.

**Marcol** · 06-21-2012, 11:10 AM

Try this ...

Please Login or Register  to view this content.

**Marcol** · 06-21-2012, 11:28 AM

@ weeble33

LEFT(B1,1) will always return a string. To return a number you need to multiply by 1, or add 0

Please Login or Register  to view this content.

Please Login or Register  to view this content.

LEFT(),MID(), and RIGHT() are all string handling functions, all return strings, never numbers.

**weeble33** · 06-21-2012, 11:34 AM

Ah, yea. I did a Cell() function and determined it was returning a string, just didn't know how to coerce a number.

Well, for what it's worth I believe this works to:

Please Login or Register  to view this content.

Copy frist six characters of a string only if first character is non numeric in cell

LinkBack

Thread Tools

Rate This Thread

Display

Copy frist six characters of a string only if first character is non numeric in cell

Re: Copy frist six characters of a string only if first character is non numeric in cell

Re: Copy frist six characters of a string only if first character is non numeric in cell

Re: Copy frist six characters of a string only if first character is non numeric in cell

Re: Copy frist six characters of a string only if first character is non numeric in cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions