Isolating information within a cell

[cDuck28Z]

Hey, i've got a series of numbers that are not all in the same format:

01-1001 A
01-1002 A
01-1003 B
011004-A
011005-B
01-1006 C


I want it to look like:

011001.....A
011002.....A
011003.....B
011004.....A
011005.....B
011006.....C


I've gotten this far using 'Text to Columns" and separating it by the " " [space]. I can't seem to get the "-" to do what i want though because they appear in both formats:

01-1001.....A
01-1002.....A
01-1003.....B
011004-A
011005-B
01-1006.....C

Is there a way to select the cell that contain only "##-###" and not the "######-"?? There are over 5000 data entries so would really prefer the computer to do this for me..

[NBVC]

Perhaps?

Assuming first entry is in A1,

In B1:

=REPLACE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),C1,""),3,0," ")

in C1:

=RIGHT(A1)

both copied down.

[JBeaucaire]

1) Put data in column A.

2) In B1 put this formula and copy down:
=LEFT(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),6)

3) In C1 put this formula and copy down:
=RIGHT(A1,1)

[Domski]

Or using a macro:

Please Login or Register  to view this content.

Dom

[Leith Ross]

Hello cDuck28Z,

Here is another VBA macro. This assumes the data is in column "A" of the ActiveSheet. It will replace the values that match your criteria.

Please Login or Register  to view this content.

[cDuck28Z]

1) Put data in column A.

2) In B1 put this formula and copy down:
=LEFT(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),6)

3) In C1 put this formula and copy down:
=RIGHT(A1,1)

Sorry, i should have provided better example data. There are not only letters but also numbers; ex:

01-0100 01
01-0102 01
010103-02
01-1001 A
01-1002 A
01-1003 B
011004-A
011005-B
01-1006 C
01-1007 02
011008-01



Everything is good, but if my number is: 99-0199 01, my output becomes 99 99.....01. It seems to delete the "01" AFTER the "-" if the last two numbers are also 01

I've included a copy of the file i am working on. See cell B1 for an example i created or cell B43:B44 for the original problem example (both are the same, i just created an example at the top to make things easier)

[NBVC]

It looks like you are using the formulas I provided... if so, change the formula in B1 to:

=REPLACE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),C1,"",(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)),3,0," ")

copied down...

[Leith Ross]

Hello cDuck28Z,

The VBA solution I posted will deal with alpha-numeric strings at the end. If you are looking for a formula based answer then you should post your question to the Excel General forum.

[NBVC]

I also forgot to mention to change the formula in C1 to:


=TRIM(RIGHT(A1,2)) to get rid of space and work with new formula in B1....

[cDuck28Z]

It looks like you are using the formulas I provided... if so, change the formula in B1 to:

=REPLACE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),C1,"",(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)),3,0," ")

copied down...

Works Perfectly!! Thank you!!!

Works ALMOST perfectly!! Found one error. Check out cell C 102 with the new formula. It keeps the "-" in column C, and doesn't remove the "B" after the numbers and dash..?


..New file uploaded with new formulas from posts

[NBVC]

In C1:

=TRIM(SUBSTITUTE(RIGHT(A1,2),"-",""))

copied down.

[cDuck28Z]

In C1:

=TRIM(SUBSTITUTE(RIGHT(A1,2),"-",""))

copied down.

Got it!!! =D

[NBVC]

Can you give me an example?