Copy one cell. Tp a cell; that has a sentence

[ABRAMS_C]

Hey Guys. This is what I have.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

A

B

C

<colgroup><col style="width: 316px"><col width="180"><col width="625"></colgroup><tbody>
</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

United States Steel Corporation (U.S. Steel)

info@uss.com

Hi, I’m trying to figure out who oversees freight and shipping there at U.S. Steel. Would you mind pointing me towards the right person please, and the best way I might get in touch with them?

<colgroup><col style="width: 316px"><col width="180"><col width="625"></colgroup><tbody>
</tbody>

I want to take ALL of the word from A and insert into C between "at" & "."
I have several hundred rows. So I want to be able to insert a formula and fill down.

Do I use "LEFT", "REPLACE", or what?

Thanks a bunch.

 

[jtakw]

Hi,

This will work for your posted sample, but don't know if it'll fail for the rest of your data since there's only 1 sample, unless All the rest of the data are identical except for the Company.

Also, if you're building Generic Content trying to address different Companies, it will probably be better if you have the Generic language in 2 separate cells, then Concatenate the cells with the cell containing the company name (as in my sample C6, C7, C8).

Book1
	A	B	C
1	United States Steel Corporation (U.S. Steel)		Hi, Im trying to figure out who oversees freight and shipping there at U.S. Steel. Would you mind pointing me towards the right person please, and the best way I might get in touch with them?
2			Hi, Im trying to figure out who oversees freight and shipping there at United States Steel Corporation (U.S. Steel). Would you mind pointing me towards the right person please, and the best way I might get in touch with them?
3			Hi, Im trying to figure out who oversees freight and shipping there at United States Steel Corporation (U.S. Steel). Would you mind pointing me towards the right person please, and the best way I might get in touch with them?
4
5
6			Hi, Im trying to figure out who oversees freight and shipping there at
7			. Would you mind pointing me towards the right person please, and the best way I might get in touch with them?
8			Hi, Im trying to figure out who oversees freight and shipping there at United States Steel Corporation (U.S. Steel). Would you mind pointing me towards the right person please, and the best way I might get in touch with them?
Sheet37

Cell Formulas
Range	Formula
C2	=SUBSTITUTE(C1,MID(C1,SEARCH("at",C1)+3,SEARCH("Would",C1)-SEARCH("at",C1)-6),A1)
C3	=REPLACE(C1,SEARCH("at",C1)+3,SEARCH("Would",C1)-SEARCH("at",C1)-6,A1)
C8	=CONCATENATE(C6,A1,C7)

 

[Peter_SSs]

Welcome to the MrExcel board!

I would also use SUBSTITUTE but have the blank message in a separate cell with the insertion point clearly marked (I've used the # symbol - see cell D1) so that there is less likelihood of an error if the message is changed and included, say, "I wonder what you are doing at United States Steel Corporation (U.S. Steel)". The formula also becomes much simpler.

Excel Workbook
	A	B	C	D
1	United States Steel Corporation (U.S. Steel)		Hi,Im trying to figure out who oversees freight and shipping there at United States Steel Corporation (U.S. Steel). Would you mind pointing me towards the right person please, and the best way I might get in touch with them?	Hi,Im trying to figure out who oversees freight and shipping there at #. Would you mind pointing me towards the right person please, and the best way I might get in touch with them?
2	Another Co		Hi,Im trying to figure out who oversees freight and shipping there at Another Co. Would you mind pointing me towards the right person please, and the best way I might get in touch with them?
Insert Co Name