How to extract character after second hyphen(-) using formula
Looking out to extract character after second hyphen from a column in a excel.
col1 Col2
BEQ-REG NO 2547-DG225647 INR 287649499.75/GST
RCN-REG NO 0188-ST224536 INR 16784398.75/NPM
DSC-REG NO 0266-BC115677 INR 8757323.50/NS
PST-REG NO 2077-DG887536 INR 9432789.50/HR
DSC-REG NO 3066-DX256788 INR 556798/#
Expected Output
col1 Col2
BEQ-REG NO 2547-DG225647 INR 287649499.75/GST DG225647
RCN-REG NO 0188-ST224536 INR 16784398.75/NPM ST224536
DSC-REG NO 0266-BC115677 INR 8757323.50/NS BC115677
PST-REG NO 2077-DG887536 INR 9432789.50/HR DG887536
DSC-REG NO 3066-DX256788 INR 556798/# DX256788
Please suggest how i can extract values from col1 to col2
=TRIM(MID(A2,FIND("#",SUBSTITUTE(A2,"-","#",2))+1,255))
Solution 1:
If the string is always 8 characters long, you can use:
=MID(A2,FIND("#",SUBSTITUTE(A2,"-","#",2))+1,8)
If it is of variable length the following will work, assuming you want everything after the second "-
" and the first space:
=LEFT(MID(A2,FIND("#",SUBSTITUTE(A2,"-","#",2))+1,255),FIND(" ",MID(A2,FIND("#",SUBSTITUTE(A2,"-","#",2))+1,255)))