Copy cells based of certain text or blanks spaces?

[jbarrick007]

Im looking for help with combining formulas in the right order to reach my goal. But im uncertain of how to combine IF, SEARCH, or others together for my goal. Thanks for the help.

Please see below.
Goal - Copy F4 to D4 and F5 to E5 (typical for a table containing 150 rows)
All F4 types cells are different because they have 6 blank spaces before the text, and outline numbers are x.x (2 digits)
All F5 types cells are different because they have 9 blank spaces before the text, and outline numbers are x.x.x (3 digits)
Its pretty straight forward how to copy F3 to C3, but Thought I would point out that is part of what I am doing to. F3 has zero spaces.

	(A) WorkFlow #	(B) Outline #	(C) Phase	(D) Milestones	(E) Tasks	(F) Events
Row 3	Phase 2	2	Planning			Planning
Row 4	2.01	2.1				PIR Adoption
Row 5	2.1.1					Submit PIR to FCR

 

[jbarrick007]

Forgive the editing above if column F it is suppose to look like...
F3 - "Planning" - zero spaces
F4 - " PIR Adoption" - six spaces
F5 - " Submit PIR to FCR" - nine spaces

 

[Fluff]

Maybe

+Fluff New.xlsm
	A	B	C	D	E	F
4	(A) WorkFlow #	(B) Outline #	(C) Phase	(D) Milestones	(E) Tasks	(F) Events
5	Phase 2	2	Planning			Planning
6	2.01	2.1		PIR Adoption		PIR Adoption
7	2.1.1				Submit PIR to FCR	Submit PIR to FCR
Test

Cell Formulas
Range	Formula
C5:C7	C5	=IF(LEFT(F5,1)<>" ",F5,"")
D5:D7	D5	=IF(AND(LEFT(F5,6)=REPT(" ",6),LEN(B5)=3),F5,"")
E5:E7	E5	=IF(LEFT(F5,9)=REPT(" ",9),F5,"")

 

[jbarrick007]

Maybe

+Fluff New.xlsm
	A	B	C	D	E	F
4	(A) WorkFlow #	(B) Outline #	(C) Phase	(D) Milestones	(E) Tasks	(F) Events
5	Phase 2	2	Planning			Planning
6	2.01	2.1		PIR Adoption		PIR Adoption
7	2.1.1				Submit PIR to FCR	Submit PIR to FCR
Test

Cell Formulas
Range	Formula
C5:C7	C5	=IF(LEFT(F5,1)<>" ",F5,"")
D5:D7	D5	=IF(AND(LEFT(F5,6)=REPT(" ",6),LEN(B5)=3),F5,"")
E5:E7	E5	=IF(LEFT(F5,9)=REPT(" ",9),F5,"")

Hey that worked great!!!
Would it be easy enough to remove those spaces once i moved them over?

 

[Fluff]

Yup, you can use trim, like

+Fluff New.xlsm
	A	B	C	D	E	F
4	(A) WorkFlow #	(B) Outline #	(C) Phase	(D) Milestones	(E) Tasks	(F) Events
5	Phase 2	2	Planning			Planning
6	2.01	2.1		PIR Adoption		PIR Adoption
7	2.1.1				Submit PIR to FCR	Submit PIR to FCR
Test

Cell Formulas
Range	Formula
C5:C7	C5	=IF(LEFT(F5,1)<>" ",F5,"")
D5:D7	D5	=IF(AND(LEFT(F5,6)=REPT(" ",6),LEN(B5)=3),TRIM(F5),"")
E5:E7	E5	=IF(LEFT(F5,9)=REPT(" ",9),TRIM(F5),"")

 

[jbarrick007]

Yup, you can use trim, like

+Fluff New.xlsm
	A	B	C	D	E	F
4	(A) WorkFlow #	(B) Outline #	(C) Phase	(D) Milestones	(E) Tasks	(F) Events
5	Phase 2	2	Planning			Planning
6	2.01	2.1		PIR Adoption		PIR Adoption
7	2.1.1				Submit PIR to FCR	Submit PIR to FCR
Test

Cell Formulas
Range	Formula
C5:C7	C5	=IF(LEFT(F5,1)<>" ",F5,"")
D5:D7	D5	=IF(AND(LEFT(F5,6)=REPT(" ",6),LEN(B5)=3),TRIM(F5),"")
E5:E7	E5	=IF(LEFT(F5,9)=REPT(" ",9),TRIM(F5),"")

Hello Fluff,
You are so helpful. Im know a fair bit, but not really compared to what excel can do. I will pay forward helping others the way you are.

Can I ask you one last question for this thread???
I know how to Insert a bullet point symbol by adding -- =" •"&IF(AND(LEFT(F6,6)=REPT(" ",6),LEN(B6)=3),TRIM(F6),"") or -- =" ○ "&IF(LEFT(F7,9)=REPT(" ",9),TRIM(F7),"")
But doing that adds the bullet points to every line when I only want to do it to the lines that copy info over.
So how do I use the ISBLANK or LEFT funtions to add the symbols only on the line that copy? So if you ar elooking at the same example above and the code =" •"&IF(AND(LEFT(F6,6)=REPT(" ",6),LEN(B6)=3),TRIM(F6),"") it would only add the bullet point to cell D6?

Thank you again so much.

 

[jbarrick007]

Hello Fluff,
You are so helpful. Im know a fair bit, but not really compared to what excel can do. I will pay forward helping others the way you are.

Can I ask you one last question for this thread???
I know how to Insert a bullet point symbol by adding -- =" •"&IF(AND(LEFT(F6,6)=REPT(" ",6),LEN(B6)=3),TRIM(F6),"") or -- =" ○ "&IF(LEFT(F7,9)=REPT(" ",9),TRIM(F7),"")
But doing that adds the bullet points to every line when I only want to do it to the lines that copy info over.
So how do I use the ISBLANK or LEFT funtions to add the symbols only on the line that copy? So if you ar elooking at the same example above and the code =" •"&IF(AND(LEFT(F6,6)=REPT(" ",6),LEN(B6)=3),TRIM(F6),"") it would only add the bullet point to cell D6?

Thank you again so much.

I finally figured it out. --- =IF(LEFT(F7,9)=REPT(" ",9),TRIM(" ○"&F7),"")

 

[jbarrick007]

I finally figured it out. --- =IF(LEFT(F7,9)=REPT(" ",9),TRIM(" ○"&F7),"")

Well I jumped the gun, that solved the bullets points only being with text and not every row, but It isnt reincluding the spaces.

 

[Fluff]

Try it like
=IF(AND(LEFT(F5,6)=REPT(" ",6),LEN(B5)=3)," • "&TRIM(F5),"")

 

[jbarrick007]

thank you very much. I knew it was just the ordering of things but for some reason wasn't getting it correct.