Unable to split text and numbers into Separate Cells that have no space

**BDavis** · 01-21-2013, 02:03 PM

Good Morning!

I have been searching for a method to split cells that have text and numbers combined with no spaces between them. I have a sheet that has a column of codes, with varying lengths of text, that I need to split into a column of text and column of Numbers.

I have attached a very basic spreadsheet to explain what I am trying to do.

After I select get the text and numbers separated, I need to pull the last 3 digits (from the Left) and the 4th-7th digits (from the left) into their own columns.

The cells look something like this: They are originally located in column C2:C800.

EANENE10316080
EANENE10316080
EANENE10316080
EANENE10316080
EANENE10316080
EANENE10322083
EANENE10322083
DPDPDP00265081

I would like it to look like this (example cell: EANENE10316080)

D1 E1 F1

EANENE 080 10316

I have used the following code to pull the number out: but, I can't figure out how to pull the text out:

=MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),LEN(C2))

Any help would be greatly appreciated! Thank you in advance.

**Miraun** · 01-21-2013, 02:06 PM

Could use use text-to-columns under Data ribbon, and use Fixed Width... if it's always a 6-5-3 split.

**oeldere** · 01-21-2013, 02:12 PM

If it's always that way.

See the file for the formula's

b1

=Left($A1,6)

c1

=Right(Left($A1,10),3)

d1

=Right($A1,6)

**BDavis** · 01-21-2013, 02:17 PM

Thank you for the quick reply. The text length can vary from 5-8 characters. I apologize that I did not put that in my original post. That makes it more challenging to use the text to columns.

Any other direction to pursue would be greatly appreciated.

Thank you again!

**Ron Coderre** · 01-21-2013, 02:18 PM

Since you indicated that the text length can be variable...
Using your posted workbook...

The numeric component:

E2: =TRIM(MID(C2,MIN(INDEX(FIND({0,1,2,3,4,5,6,7,8,9},C2&"0123456789"),0)),LEN(C2)))

the text values:

D2: =LEFT(TRIM(C2),LEN(TRIM(C2))-LEN(E2))

The last 3 digits

F2: =RIGHT(E2,3)

The digits preceding the last 3 digits

G2: =LEFT(E2,LEN(E2)-3)

Copy those formulas down through Row_9

These will be the results:

EANENE10316080                        EANENE        10316080        080        10316
EANENE10316080                        EANENE        10316080        080        10316
EANENE10316080                        EANENE        10316080        080        10316
EANENE10316080                        EANENE        10316080        080        10316
EANENE10316080                        EANENE        10316080        080        10316
EANENE10322083                        EANENE        10322083        083        10322
EANENE10322083                        EANENE        10322083        083        10322
DPDPDP00265081                        DPDPDP        00265081        081        00265

Note: the Col_C values have many trailing spaces.

Is that something you can work with?

**BDavis** · 01-21-2013, 02:39 PM

Ron,

Thank you very much! I really appreciate the help! That is exactly what I needed!

Thank you again for the quick reply!

Have a great day!

**BDavis** · 01-21-2013, 03:03 PM

Ron,

If you don't mind... and you have a moment, I have not figured out all of the 'functions' and how to use them most appropriately. What was the purpose of using TRIM in your following code and your subsequent codes?

E2: =TRIM(MID(C2,MIN(INDEX(FIND({0,1,2,3,4,5,6,7,8,9},C2&"0123456789"),0)),LEN(C2)))

D2: =LEFT(TRIM(C2),LEN(TRIM(C2))-LEN(E2))

Thanks again. Really appreciate your help.

**Ron Coderre** · 01-21-2013, 04:39 PM

I think I can best describe the TRIM function with an example:

A1: ="     abc        def        "
A2: =LEN(A1)

A3: =TRIM(A1)
A4: =LEN(A3)

I hope that helps

Unable to split text and numbers into Separate Cells that have no space

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Unable to split text and numbers into Separate Cells that have no space

Re: Unable to split text and numbers into Separate Cells that have no space

Re: Unable to split text and numbers into Separate Cells that have no space

Re: Unable to split text and numbers into Separate Cells that have no space

Re: Unable to split text and numbers into Separate Cells that have no space

Re: Unable to split text and numbers into Separate Cells that have no space

Re: Unable to split text and numbers into Separate Cells that have no space

Re: Unable to split text and numbers into Separate Cells that have no space

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions