Forum Discussion
Excel Add Text after first character _
Hi
I was asking people to help me with adding text and although I hoped they may have answer none of 30 excel day to day excel people could help me and I am hoping that there is bigger expert here so they may give me some hints
I have test like this
DVS.101_SERVICE-LAN-IN_172.16.25.1
DVS.2047_INTERMEDIA-WEB_test
DVS.2070_cCFS_DMZExt_192.168
Now I only need to add work SYN after first red character. I have used Kutools and although good it adds this word after every _ character which is not what I want
I would need to add only word SYN after first red character. It doesnt work by position because some have 8 or 9 position so that wouldnt be the patern
NOt sure if excel can look and only add that word after first _ character and not the rest of the cell
6 Replies
Maybe your "_" is in ASCII code point.
Try this:
=SUBSTITUTE(A2, CHAR(95), "_SYN", 1)
Let's say you have such a text in A2.
In B2, enter the formula
=SUBSTITUTE(A2, "_", "_SYN", 1)
This can be filled down. Or if you want to fill rows 2 to 100 in one go, use
=SUBSTITUTE(A2:A100, "_", "_SYN", 1)
it works only if the sheet is new if I try to do it in another sheet then after I press enter it just puts substitute text and it doesnt replace
after i put formula in new sheet this replacement text
=SUBSTITUTE(A2,"_","_SYN01_",1)
for some sheets it works for some doesnt
It should work on all sheets. Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
HI
Yes it is working I made little mistake but this is working as expected
thanks a ton
after entering this into B2
=SUBSTITUTE(A2, "_", "_SYN", 1)
it still makes the same word without word SYN is anything else that needs to be done
