How to insert some value at a perticular postion

[thenvk]

I have cells in the following way. in each row.


abcd
efgh
a
dfg
erft
vkldfsdas

Now I want to insert some value say XXXXX at the first and last postion(More specifically at a perticular position) in the text). How can I do it?

The end result should be

XXXXX abcdXXXXX
XXXXX efghXXXXX
XXXXX aXXXXX
XXXXX dfgXXXXX
XXXXX erftXXXXX
XXXXX vkldfsdasXXXXX

 

[Stormseed]

Welcome to MrExcel.

If your data starts in cell A1, you could try:

="xxxxxx"&" "&A1&"xxxxxx"

 

[thenvk]

I want in a find and replace kind of thing. Actually I am a test engineer and will use it in replacing test cases.
If I use the above formula, it would not work for me.

 

[Stormseed]

Is the text which you want to add going to be the same all the way through all your rows ?

 

[thenvk]

Yes, its same....

Thanks a lot for such a quick response

 

[Stormseed]

Yes, its same....

Thanks a lot for such a quick response
<!-- / message -->

Then the formula which is posted above woould work fine enough. Did you try the formula on your worksheet / file ?

 

[thenvk]

Is there any other alternate option like.. insert a perticular text(Say "The") at the starting position of every cell and put a '.' (Full stop) at the end of text?

 

[Stormseed]

See the table below, I have put the formula starting in cell B1 and copiied down to the end of data in column A. Please let me know if you still cannot understand what I am trying to tell you:

Book1
	A	B	C	D
1	Sometext	Resultoftheformula
2	sometextinthiscell	Thesometextinthiscell.
3	moretextincellA1	ThemoretextincellA1.
4	thisisjustanexample	Thethisisjustanexample.
5	toshowthetextincolA	ThetoshowthetextincolA.
Sheet2

 

[thenvk]

I think you did not get my point.I understood what you have mentioned in the above post.
I want the replacements in the same cell. Not in the next cell,by referring it.

 

[Stormseed]

Yeah, but that replacements in the adjacent cells are not permanent. You can make them permanent by copying the cells in column B to column A permanently.

Is that what you look forward to accomplish ? I cannot tell you anything about the "Replace" option because I am not sure of that and I am not in a mood to test that right now