I am trying to figure out to split a cell formated with a numeric and alpha
format. For example, the format is "10100 Accounts Payable"

I want to split the cells to "10100" (1st cell) then "Accounts Payable" (2nd
cell).

I have 2,700 cells to split, so I need efficient commands. Thanks.

Assume your original values are in column A starting on row 1:

In B1: =LEFT(A1,SEARCH(" ",A1)-1)
In C1: =SUBSTITUTE(A1,B1&" ","")

--
Regards,
Dave


"FFB PFK" wrote:

I am trying to figure out to split a cell formated with a numeric and alpha
format. For example, the format is "10100 Accounts Payable"

I want to split the cells to "10100" (1st cell) then "Accounts Payable" (2nd
cell).

I have 2,700 cells to split, so I need efficient commands. Thanks.

Any rhyme or reason to the numbers?

All 5 digit or not?

DataText to Columns will do the trick using Fixed Width if all digits are
same length.


Gord Dibben Excel MVP

On Tue, 20 Dec 2005 12:06:02 -0800, "FFB PFK" <FFB
wrote:

I am trying to figure out to split a cell formated with a numeric and alpha
format. For example, the format is "10100 Accounts Payable"

I want to split the cells to "10100" (1st cell) then "Accounts Payable" (2nd
cell).

I have 2,700 cells to split, so I need efficient commands. Thanks.

Hi,

You may try the following array formula (Ctrl+****+Enter)

Assume that "10100 Accounts payable" is entered in cell A14. In B14 enter
the following array formula

=1*MID(A14,MATCH(TRUE,ISNUMBER(1*MID(A14,ROW($1:$1 9),1)),0),COUNT(1*MID(A14,ROW($1:$19),1))+IF(ISNUM BER(MATCH(".",MID(A14,ROW($1:$19),1),0)),1,0))

In cell D14, enter the following array formula

=MID(A14,MATCH(TRUE,ISERROR(1*MID(A14,ROW($1:$51), 1)),0),100+IF(ISERROR(MATCH(".",1*MID(A14,ROW($1:$ 51),1),0)),1,0))

I hop this helps. Please let me know if you need any further help on this.

My e-mail address is

Regards,


"FFB PFK" wrote:

I am trying to figure out to split a cell formated with a numeric and alpha
format. For example, the format is "10100 Accounts Payable"

I want to split the cells to "10100" (1st cell) then "Accounts Payable" (2nd
cell).

I have 2,700 cells to split, so I need efficient commands. Thanks.

Hi,

Another option..

In B1,

=LOOKUP(9.999999999E+307,--MID(A1,1,ROW($1:$1024)))

In C1,

=TRIM(SUBSTITUTE(A1,B1,""))

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=494996