formulas needed

[ericlch16]

I have a column which has these

128982 VC AAAA
1389883; MC AAAA
1589 DC BBBB
179024;VC DDDDD
1212313;VC EEEEE
18902 MC FFFFF

I want a formula in another column to include all the numbers i.e

128982
1389883
1589
179024
1212313
18902

is there a formula i can use in another column to get all the numbers before the ; or empty space?

the delimiter does not work as users are not consistent.. sometime they put ; or empty space between the numbers which varies in length and the VC/MC/DC

 

[nancymk]

as long as there is always either a semi-colon or space, this should work

=IF(ISERROR(FIND(";",A1)),LEFT(A1,FIND(" ",A1)-1),LEFT(A1,FIND(";",A1)-1))

 

[Dannyh1]

=if(iserror(find(";",a1)),left(a1,find(" ",a1)-1),left(a1,find(";",a1)-1))

 

[MrKowz]

Try:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">128982 VC AAAA</td><td style="text-align: right;;">128982</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">1389883; MC AAAA</td><td style="text-align: right;;">1389883</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1589 DC BBBB</td><td style="text-align: right;;">1589</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">179024;VC DDDDD</td><td style="text-align: right;;">179024</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">1212313;VC EEEEE</td><td style="text-align: right;;">1212313</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">18902 MC FFFFF</td><td style="text-align: right;;">18902</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=LEFT(<font color="Blue">A1,MIN(<font color="Red">FIND(<font color="Green">" ",A1&" "</font>),FIND(<font color="Green">";",A1&";"</font>)</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[T. Valko]

I have a column which has these

128982 VC AAAA
1389883; MC AAAA
1589 DC BBBB
179024;VC DDDDD
1212313;VC EEEEE
18902 MC FFFFF

I want a formula in another column to include all the numbers i.e

128982
1389883
1589
179024
1212313
18902

is there a formula i can use in another column to get all the numbers before the ; or empty space?

the delimiter does not work as users are not consistent.. sometime they put ; or empty space between the numbers which varies in length and the VC/MC/DC

Here's another one...

This version will return the number as a TEXT string:

=LEFT(A2,FIND(" ",SUBSTITUTE(A2,";"," "))-1)

This version will return the number as a numeric number (however, if there are leading zeros they will be removed!):

=--LEFT(A2,FIND(" ",SUBSTITUTE(A2,";"," "))-1)

 

[ericlch16]

Thanks it helps. I had so many choices... Now what if I want another column for

VC
MC
DC
VC
VC
MC

for the one column which has data below

128982 VC AAAA
1389883; MC AAAA
1589 DC BBBB
179024;VC DDDDD
1212313;VC EEEEE
18902 MC FFFFF

what formula should i use?

 

[MrKowz]

Try to, instead of formulas, use Text-To-Columns. Have it delimit on semicolon and space, and to treat consecutive delimiters as one.

 

[ericlch16]

text delimiter does not work because of data entry inconsistency..


is there a formula to do it instead like Find(MC, )?

there are only MC, DC, AM, VC options

which stands for mastercard, debit card, american express, visa card.

 

[T. Valko]

Thanks it helps. I had so many choices... Now what if I want another column for

VC
MC
DC
VC
VC
MC

for the one column which has data below

128982 VC AAAA
1389883; MC AAAA
1589 DC BBBB
179024;VC DDDDD
1212313;VC EEEEE
18902 MC FFFFF

what formula should i use?

Try this...

=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,";"," ")," ",REPT(" ",100)),200),100))

 

[MrKowz]

Text delimiter worked for me.

I started out with this data:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">128982 VC AAAA</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">1389883; MC AAAA</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1589 DC BBBB</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">179024;VC DDDDD</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">1212313;VC EEEEE</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">18902 MC FFFFF</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br />

And then after I used text-to-columns using the aforementioned method, it returned:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">128982</td><td style=";">VC</td><td style=";">AAAA</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1389883</td><td style=";">MC</td><td style=";">AAAA</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1589</td><td style=";">DC</td><td style=";">BBBB</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">179024</td><td style=";">VC</td><td style=";">DDDDD</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1212313</td><td style=";">VC</td><td style=";">EEEEE</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">18902</td><td style=";">MC</td><td style=";">FFFFF</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br />