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

dankar

Board Regular
Joined
Mar 23, 2016
Messages
71
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>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,198
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Cell B1 is formatted as "mm/dd/yyyy"
Excel Workbook
AB
1D: 05/08/201805/08/2018
Sheet2
 

dankar

Board Regular
Joined
Mar 23, 2016
Messages
71
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
 

dankar

Board Regular
Joined
Mar 23, 2016
Messages
71
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:

Jborg

Board Regular
Joined
Feb 3, 2012
Messages
160

ADVERTISEMENT

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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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,"")
 

dankar

Board Regular
Joined
Mar 23, 2016
Messages
71

ADVERTISEMENT

thank you very much...worked perfectly!
 

dankar

Board Regular
Joined
Mar 23, 2016
Messages
71
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!
 

dankar

Board Regular
Joined
Mar 23, 2016
Messages
71
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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,"")
 

Watch MrExcel Video

Forum statistics

Threads
1,108,731
Messages
5,524,507
Members
409,584
Latest member
RedHelp

This Week's Hot Topics

Top