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

dankar

Board Regular
Joined
Mar 23, 2016
Messages
113
Office Version
  1. 365
Platform
  1. Windows
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
D: 05/08/201805/08/2018

<tbody>
</tbody>
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Thanks for your reply and help.

not always formatted as "mm/dd/yyyy"

so I want any thing after the D: to be pasted with the same format,

thank you


Cell B1 is formatted as "mm/dd/yyyy"
Sheet2

AB
1D: 05/08/201805/08/2018

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 92px;"><col style="width: 75px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B1=RIGHT(A1,LEN(A1)-FIND("D: ",A1)-2)+0

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
so I tried your formula but the answer was number (I had to change the format cell to date to get the date format)..if possible to paste it as text ..would this work

and it word fine with me..but I found that some have this date ( D: 0/0/0) and your formula didn't paste this the answer was #Value ! which I don't know why
 
Last edited:
Upvote 0
If your text is in A1 put this formula in B1
=SUBSTITUTE(A1,MID(A1,1,SEARCH(":",A1,1)+1),"")

Is this what you want?
 
Last edited:
Upvote 0
For the formula Joe posted in Message #2 , simply remove the +0 and it will return the value you want.

In addition to that, here is another formula that you can consider...

=REPLACE(A1,SEARCH("D:",A1),3,"")
 
Upvote 0
thank you for you reply after removing the +0 from Joe formula it worked perfect ( still don't know why the +0 was for)
but you formula didn't work with me because I have many things before the ("D:") I have a long text so I wanted to search for the ("D:" and paste what after it)

your formula pasted all the text except for the ("D:")
sorry I didn't made my request clear..thank you though!
 
Upvote 0
is there a way to use a formula to paste any thing between two words...for example I have a long comment and I want to paste any thing between two word I will indicate.

thanks again for the help
 
Upvote 0
thank you for you reply after removing the +0 from Joe formula it worked perfect ( still don't know why the +0 was for)
Dates, to Excel, are actually floating point numbers... the whole number part is the number of days offset the day before 1/1/1900 and the decimal part is the fraction of a 24-hour day represented by the time. In Excel, any text number is automatically converted to a real number when it is involved in a mathematical operation. Adding zero to any number does not change the number, so adding zero to a text date converts it to the actual number of days it is offset from the day before 1/1/1900 (which you found out needed to be formatted as a Date in order to see the month, day and year in a familiar format).




but you formula didn't work with me because I have many things before the ("D:") I have a long text so I wanted to search for the ("D:" and paste what after it)

your formula pasted all the text except for the ("D:")
I did not realize you had more text in front of the "D:". Given that, this version of my formula should work for you then...

=REPLACE(A1,1,SEARCH("D:",A1)+2,"")
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top