finding a wotd in cell and pasting what after it in the required cell

[dankar]

Hi,

I need to find a word in the cell and paste the part after it in the cell

I need to find "D:" and paste what after it in the cell

thank you
[TABLE="width: 500"]
<tbody>[TR]
[TD]D: 05/08/2018[/TD]
[TD]05/08/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dankar]

Thanks again for the help and explination,

I need more help if possible..

so my comments have many things and its saved like this :

ID: D66-1328
NO: II-550-254
PR: 1ABC
D: 6/15/67

and I have columns divided by ID, NO, PR, Date

[TABLE="width: 500"]
<tbody>[TR]
[TD]COMMENTS[/TD]
[TD]ID[/TD]
[TD]NO[/TD]
[TD]PR[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]ID: D66-1328
NO: II-550-254
PR: 1ABC
D: 6/15/67[/TD]
[TD]D66-1328[/TD]
[TD]II-550-254[/TD]
[TD]1ABC[/TD]
[TD]6/15/67[/TD]
[/TR]
[TR]
[TD]NO: II-550-254
PR: 1ABC
D: 6/15/67[/TD]
[TD][/TD]
[TD]II-550-254[/TD]
[TD]1ABC[/TD]
[TD] 6/15/67[/TD]
[/TR]
</tbody>[/TABLE]

Sometimes the comments does not include all of them.

so for the previous formula I used it to paste the date under DATE.

am supposed to paste the ID: in the ID section etc..

I can use filter and use column by column.

but is there a way to paste what after the ID for example and before the ENTER or if what before the NO is a space..then any thing after the ID: and before the first space.

or if not possible at least : to paste anything between a word I indicate and another word I indicate.

thank you and sorry for the long msg.

 

[Rick Rothstein]

so my comments have many things and its saved like this :

ID: D66-1328
NO: II-550-254
PR: 1ABC
D: 6/15/67

Are these all in one cell?

 

[dankar]

yes..
and when you click on the cell they look like this not as one line

 

[dankar]

it will work "kind of" if I used your previous formula (instead of "D:" I put "PR:") and I got the answer (1ABCD: 6/15/67) so If I can add something to delete any thing after the "D:" this will help also.

 

[Rick Rothstein]

it will work "kind of" if I used your previous formula (instead of "D:" I put "PR:") and I got the answer (1ABCD: 6/15/67) so If I can add something to delete any thing after the "D:" this will help also.

Assuming you have the headers you show in Row 1, you can put this formula in cell B2 and copy it across to cell D2 (note that one column before the Date column) and then copy the range B2:D2 down to the end of your data. That will handle all but the date. The problem with the date values is it starts and ends with "D:" which is the same as the end of the ID marker, so we can't just search for "D:" as we will find "ID:" first. We will need a separate formula for that column. There are several way to approach the formula for the date value, so I have some questions about your comments...

1) Will the comments always contain a date?

2) If a comment has a date, will it always be the last item in the list?

 

[dankar]

1) yes
2)yes

sorry for the confusion so I was giving an example (the Marker ID is DASH in the comments):
DASH: D66-1328
NO: II-550-254
PR: 1ABC
D: 6/15/67

and I thought after using your formula I got this answer 1ABCD: 6/15/67 so I thought we can add something to remove anything after the D: ( I can copy and paste the answers in the first step which will eliminate everything except for the data. and then we can use another formula to deleted any thing after the D: if possible.

sorry for all the confusion.

 

[Rick Rothstein]

sorry for the confusion so I was giving an example (the Marker ID is DASH in the comments):
DASH: D66-1328
NO: II-550-254
PR: 1ABC
D: 6/15/67

You should never "simplify" your data for us when asking a question here as we design our solutions for what you say you have. Fortunately the change to my formula to account for your using ID instead of the actual DASH code word is a simple one. Use this formula in cells B2:D2 copied down...

=IF(ISNUMBER(SEARCH(B$1&":",$A2)),MID(LEFT($A2&CHAR(10),FIND(CHAR(10),$A2,SEARCH(B$1&":",$A2))),SEARCH(B$1&":",$A2)+LEN(B$1)+2,99),"")

Put this formula in cell E2 and copy it down...

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",200)),200))

 

[dankar]

Am so sorry for that. My bad.

thank you very much for your help