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

%3CLINGO-SUB%20id%3D%22lingo-sub-1808097%22%20slang%3D%22en-US%22%3EHow%20to%20merge%20one%20cell%20and%20a%20part%20of%20a%20other%20from%20a%20specific%20sign%20or%20value.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1808097%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20data%20in%20cell%20A%20and%20need%20to%20concatenate%20it%20in%20cell%20B%20with%20part%20of%20cell%20C%20.%20Ex%3C%2FP%3E%3CP%3Ecell%20A%20%3CFONT%20color%3D%22%23FF9900%22%3E%3A%20Conny1%3A%3C%2FFONT%3E%26nbsp%3B%20cell%26nbsp%3B%20B%3A%20%3CFONT%20color%3D%22%23FF9900%22%3EConny1%3A%3C%2FFONT%3E%3CFONT%20color%3D%22%2399CC00%22%3ETIC_638.OUT%3C%2FFONT%3E%26nbsp%3B%20%26nbsp%3B%20cell%26nbsp%3B%20%26nbsp%3BC%3A%26nbsp%3B%20AW7001%2FDENS_REG%3CFONT%20color%3D%22%2399CC00%22%3E%3CFONT%20size%3D%225%22%20color%3D%22%23FF0000%22%3E%3A%3C%2FFONT%3ETIC_638.OUT%3C%2FFONT%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3Efrom%20the%20colon%20%3CFONT%20size%3D%225%22%20color%3D%22%23FF0000%22%3E%3A%3C%2FFONT%3E%20is%20where%20I%20will%20take%20the%20text%20and%20on%20wards.%20How%20do%20i%20do%20this%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1808097%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1808172%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20merge%20one%20cell%20and%20a%20part%20of%20a%20other%20from%20a%20specific%20sign%20or%20value.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1808172%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F842126%22%20target%3D%22_blank%22%3E%40Coolio1961%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20formula%3A%20%3DA1%26amp%3BRIGHT(C1%2CLEN(C1)-FIND(%22%3A%22%2CC1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20to%20familiarize%20yourself%20with%20the%20various%20text%20functions%20in%20Excel.%20%3CA%20href%3D%22https%3A%2F%2Fwww.excelfunctions.net%2Fexcel-text-functions.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.excelfunctions.net%2Fexcel-text-functions.html%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou've%20referred%20in%20your%20question%20to%20the%20term%20%22concatenate%22%20so%20perhaps%20you've%20already%20found%20that.%20I%20have%20used%20the%20ampersand%20in%20the%20formula%20above.%20You%20can%20also%20use%20various%20words%20or%20abbreviations%20(CONCATENATE%2C%20CONCAT%2C%20etc)%3C%2FP%3E%3CP%3EThen%20there%20are%20the%20ways%20to%20trim%20and%20select%2C%20which%20is%20what%20%3CSTRONG%3ERIGHT%3C%2FSTRONG%3E%20is%20doing.%20Picking%20a%20specified%20number%20of%20letters%20beginning%20at%20the%20right%20end%20of%20the%20target%20text.%3C%2FP%3E%3CP%3ETo%20figure%20out%20how%20many%20letters%2C%20I%20used%20%3CSTRONG%3ELEN(C1)%3C%2FSTRONG%3E%20to%20find%20the%20maximum%20(the%20length%20of%20the%20string%20in%20cell%20C1%3C%2FP%3E%3CP%3Eand%20from%20that%20I%20subtracted%20the%20number%20representing%20the%20point%20in%20C1%20where%20I%20found%20the%20colon%2C%20doing%20that%20by%20%3CSTRONG%3EFIND(%22%3A%22%2CC1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20a%20spreadsheet%20where%20those%20are%20all%20demonstrated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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
Highlighted

@Coolio1961 

 

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.

 

Highlighted

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

Conny