return specific character read from right to left

R

ruzaimi

Joined
Mar 27, 2014
Messages
2
Reaction score
0
I'm having problem to figure out.. what is the
formula to return a value after a specific character read from right to left?.
what i want from the data to return with Year, Month,Date

I cant use MID because the chracter length not same..

Sample data (Co_Number):
CDS-270314-001
CO-270314-001

Thanks
 
Give the following a try:

Code: 
=MID(A1,FIND("-",A1)+5,2)&MID(A1,FIND("-",A1)+3,2)&MID(A1,FIND("-",A1)+1,2)

Change A1 to the first cell that has your codes, and then you can copy the formula down. It will look for the position of the hyphen and add from there to get the starting spot for the MID formula.

Let me know how you make out :thumb:
 
Hi

Assuming that your data posted is in A1:A2, Try the following.
In B1 and copy down "=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",50)),50,50))" this returns a text value.

If you want to return a proper date use the following and format the cells as a date.
"=TEXT(TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",50)),50,50)),"00\/00\/00")+0"
 
Back
Top