Need cell truncation help

[MBCMDR]

Hello,

I have like 10,000 cells with 6 digit number comma spaced from 1 to 50 number in each cell.
Sample D1 - D12000
123456, 123457, 234567, 345678, 456789

Request is a code or cell function to truncate it, it would be nice if could removed any duplicates of the 3 digits..

123, 234, 345, 456

As I really only need the first three number.


Regards,
MBCMDR

[AlKey]

Enter formula in B1 and copy across and down

Please Login or Register  to view this content.

v	A	B	C	D	E	F	G
1	D1 - D12000 123456, 234567, 345678, 456789	123	234	345	456

[MBCMDR]

I just tried it on column 14 it placed only 1st digit and column 15 2nd, 3rd, 4rd then back working normal on column 16 then again error about next 14 columns.. I will look as think something minor to fix and work great MBCMDR

EDIT: I just noticed it also skipped the first 212111 truncate started at 213111

212111, 213111, 213121, 213131, 214525, 215111, 215811, 215814, 215816, 215819, 215822, 215841, 216111, 216131, 216141, 216151, 216191, 221112, 222121, 223112, 232111, 232411, 234111, 234311, 235141, 235166, 237111, 241111, 242211, 242212, 242213, 242223, 242231, 242241, 242311, 242511, 242711, 243211, 243221, 243411, 244112, 244116, 244117, 245113, 245119, 245142, 245172, 252914, 261630, 261811, 261812, 261821, 261822, 261831, 271111, 271311, 271811, 272112, 272311, 272811, 272813, 273811, 274111, 275111, 275131, 275151, 275161, 275811, 275821, 276211, 278131, 282211, 282212, 282231, 283111, 283112, 284211, 284212, 284231, 285013, 285071, 285081, 285082, 285346, 285360, 287511, 287523, 287821, 287841, 291111, 291114, 291122, 291131, 292132, 293111, 293211, 293311, 301111, 302111, 303111, 304111, 304112, 304211, 313161, 315111, 316441, 316461, 320911, 323111, 323511, 324211, 324212, 324611, 331114, 331316, 331318, 331319, 331431, 331611, 331612, 331617, 331622, 331626, 332341, 332411, 332621, 332811, 332821, 333811, 334111, 334211, 334311, 334411, 334421, 334691, 344315, 345311, 345611, 346114, 352111, 361111, 361131, 361151, 361152, 361211, 361221, 362211, 383211, 461320, 462118, 462119, 497411, 525111, 732111, 732116, 732119, 732120, 743111, 752311, 773111, 801111, 801121

[AlKey]

I would suggest you upload a spreadsheet so we could look for any inconsistency. It is difficult to to understand your concerns just from one small example that you provided.

[MBCMDR]

Alkey,
I love to upload but all upload are files blocked here so have to do offsite later..

Cell A1
212111, 213111, 213121, 213131, 214525, 215111, 215811, 215814, 215816, 215819, 215822, 215841, 216111, 216131, 216141, 216151, 216191, 221112, 222121, 223112, 232111, 232411, 234111, 234311, 235141, 235166, 237111, 241111, 242211, 242212, 242213, 242223, 242231, 242241, 242311, 242511, 242711, 243211, 243221, 243411, 244112, 244116, 244117, 245113, 245119, 245142, 245172, 252914, 261630, 261811, 261812, 261821, 261822, 261831, 271111, 271311, 271811, 272112, 272311, 272811, 272813, 273811, 274111, 275111, 275131, 275151, 275161, 275811, 275821, 276211, 278131, 282211, 282212, 282231, 283111, 283112, 284211, 284212, 284231, 285013, 285071, 285081, 285082, 285346, 285360, 287511, 287523, 287821, 287841, 291111, 291114, 291122, 291131, 292132, 293111, 293211, 293311, 301111, 302111, 303111, 304111, 304112, 304211, 313161, 315111, 316441, 316461, 320911, 323111, 323511, 324211, 324212, 324611, 331114, 331316, 331318, 331319, 331431, 331611, 331612, 331617, 331622, 331626, 332341, 332411, 332621, 332811, 332821, 333811, 334111, 334211, 334311, 334411, 334421, 334691, 344315, 345311, 345611, 346114, 352111, 361111, 361131, 361151, 361152, 361211, 361221, 362211, 383211, 461320, 462118, 462119, 497411, 525111, 732111, 732116, 732119, 732120, 743111, 752311, 773111, 801111, 801121

Cell B1
=IFERROR(--LEFT(TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,CHAR(10),","),",",REPT(" ",100)),100*COLUMNS($A:A),100)),3),"")

Dragged B1 out to FQ1

You see the errors.

Thank you very much..

[AlKey]

Try this
Enter in B1 and copy across

Please Login or Register  to view this content.

Small change:

Please Login or Register  to view this content.

[MBCMDR]

Thank you as see fixed the 1st value to show correctly, but in your file what would cause the error at BH, CL, DO, ES? As the code looks correct...

MBCMDR

[AlKey]

I really don't have an answer to this.
I suspect this is Excel problem. I get some errors with Excel 2016. The Excel 2010 works better but still not to my satisfaction.

[MBCMDR]

ok thank you..

When figure out will post solution as think your right just some tweak needed..

MBCMDR

[AlKey]

As workaround do Text to Column tool located under Data tab. Select comma as a delimiter. Then use formula =LEFT(A1,3) and drag it across.

v	A	B	C	D	E	F	G	H	I	J	K
1	212111	213111	213121	213131	214525	215111	215811	215814	215816	215819	215822
2	212	213	213	213	214	215	215	215	215	215	215