Moving part of a cell to another cell

[lisaboneal]

Hello, I am a basic excel user and now I have this huge conversion project and I am freaking out!

Here is my question:
I have this info in a cell:
6500-Operating & Maintenance:6515-Supplies:6515-01-Janitorial

I need to move 6515-01 to another cell. Could some kind soul help me with a formula that will do that, please?

I have the formula that will move 6515 to another cell, but I can't figure this one out.

Thanks in advance!

 

[jtakw]

Hi,

Does this help:

Book1
	A	B
1	6500-Operating & Maintenance:6515-Supplies:6515-01-Janitorial	6515-01
Sheet189

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(A1,":",REPT(" ",255),2),255,255),"-",REPT(" ",255),2),255))

 

[Rick Rothstein]

I have this info in a cell:
6500-Operating & Maintenance:6515-Supplies:6515-01-Janitorial

I need to move 6515-01 to another cell.

Will the text you want to move always follow the word "Supplies:" (with the colon after it)?

Is the text you want always four digits followed by a dash followed by two more digits?

 

[lisaboneal]

The text will always be four digits followed by a dash followed by two more digits. It will not always follow the word Supplies, but it will always follow a colon.

 

[Rick Rothstein]

The text will always be four digits followed by a dash followed by two more digits. It will not always follow the word Supplies, but it will always follow a colon.

Will it always follow the second colon as it does in the single example that you posted?

If not, will it be the only text with four digits to the left of a dash and two digits to the right of that same dash in the text in the cell?

 

[lisaboneal]

Yes, it will always follow the second colon. I have the formula for the number following the first colon, but I cannot figure out how to make it work with the number following the second colon. Thanks!

 

[Rick Rothstein]

Yes, it will always follow the second colon.

Then didn't the formula jtakw posted in Message #2 work for you?

 

[lisaboneal]

I received this formula:
=MID(A1,FIND(":",A1,FIND(":",A1,1)+1)+1,7) from another source and it works!
Is there an easy way to add to this formula to make it work if there is a fourth number behind a colon in the same text line?
I tried the other formula given here but I couldn't make it work.

 

[jolivanes]

When I tried the formula supplied by jtakw on the data supplied by you, I got this as a result:
6515-01