substitue

Hi All, I want to convert following string P4-128-HS-29039-128299-3-SP01 to 4300-SP-XH-DL-128299-128-HS-29039-03-SP01-Q Please note length of the first string may vary for example U4-126-ETT-11024-126101-10-SP01

Hi. Previously, I have posted this question How to split the name and display the status of task/project for each name?. Usually, each staff has their own task for the similar project. I tried to add additional column, which is Task and the output only recognize the first and second name of...

Hi All Have issues with this formula =VLOOKUP(FIND("*",SUBSTITUTE(A8,".","*",LEN(A8)-LEN(SUBSTITUTE(A8,".","")))),Data!B:C,2,0) What i want is to do a Vlookup on everything in the cell up to the full STOP (.). This formula is giving me the position of the last full stop...

I am trying to make a formula which basically says "find the first word that starts with G then choose the word AFTER it" but i cant configure the formula correctly, i am a bit of a novice! i can do it by combining IF statementslike this: =IF(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("G",B4),LEN(B4)),"...

Hi Experts, Any help would be appreciated in this case to removing price from given sample string, <colgroup><col></colgroup><tbody> <tbody> <colgroup><col width="1017"></colgroup><tbody> Product Bought </tbody> <colgroup><col width="753"></colgroup><tbody> Output...

Hi, I have quite a few large spreadsheets that contain unique reference numbers (both text and numbers) that I need to convert to a new format. Examples of current text number formats: ABC.1-235 LD.12-1 XYZ.356-12 Examples of what need converting to: ABC00001.235 LD00012.1 XYZ00356.12 If...

I have a file with cells containing 9932/0009 i need to convert this to 320009 so what needs to happen is remove the "/" then remove first 2 characters (99) leaving me 320009. I figured out =SUBSTITUTE(D3,"/","") (with the help of this site) and to use =RIGHT(G4,(6)) to get the right...

HI, I have sheet1(called "Timesheet") and sheet2("Legend"). Into Legend I have: <tbody> Category (columnA) Non Game (columnB) Training (columnC) Sport Value1 Training1 Non Game Value2 Training2 Training Value3 Training3 Project Value4 ... Admin ... .. </tbody> Into Manager Name I...

Hi Masters/Sifu, I have a question where I need to allocate "@" in an email address and I need to remove text after that and replace with something else: For Examples: likeaboss@boss.com likeadude@dude.com likeabrother@brother.com I need to index the "@" and remove the text after that and...

<tbody> A B C 1 clothes transportation drinks 2 pants car coffee 3 shirt train tea 4 sweater bicycle juice 5 tie plane milk </tbody> Hello everyone, I have been trying so had to make this work. I want to return "category 1" if the text is found in...