New Contributor

# How to merge one cell and a part of a other from a specific sign or value.

I have data in cell A and need to concatenate it in cell B with part of cell C . Ex

cell A : Conny1:  cell  B: Conny1:TIC_638.OUT    cell   C:  AW7001/DENS_REG:TIC_638.OUT

from the colon : is where I will take the text and on wards. How do i do this ?

2 Replies

# Re: How to merge one cell and a part of a other from a specific sign or value.

Here's the formula: =A1&RIGHT(C1,LEN(C1)-FIND(":",C1))

You need to familiarize yourself with the various text functions in Excel. https://www.excelfunctions.net/excel-text-functions.html

You've referred in your question to the term "concatenate" so perhaps you've already found that. I have used the ampersand in the formula above. You can also use various words or abbreviations (CONCATENATE, CONCAT, etc)

Then there are the ways to trim and select, which is what RIGHT is doing. Picking a specified number of letters beginning at the right end of the target text.

To figure out how many letters, I used LEN(C1) to find the maximum (the length of the string in cell C1

and from that I subtracted the number representing the point in C1 where I found the colon, doing that by FIND(":",C1)

I've attached a spreadsheet where those are all demonstrated.

# Re: How to merge one cell and a part of a other from a specific sign or value.

@mathetesMany thanks for your excellent solution i'm grateful.

Conny