remove unwanted characters from a certain range

[steve400243]

Hello, on this example I am looking for the best way to remove the unwanted characters and leave just the date in a certain column. Thanks for any help.

this range would be in starting in B2, and can be any number of rows. Results could be in Column C

B9048764-001 on 10/10/17
B9048764-002 on 10/10/17

 

[MARK858]

If your data is consistent with what you posted then try the below in C2 and copied down. Format the cells as dates.

=TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",255)),255))*1

 

[Jon Peltier]

This formula in C2 extracts the date from the end of B2:

=MID(B2,FIND(" ",B2,FIND(" ",B2)+1)+1,99)

This is not a numeric date, it's just a string of text. If you need a numeric date, you need to convert the result to a number:

=--MID(B2,FIND(" ",B2,FIND(" ",B2)+1)+1,99)

which shows 10/10/14 as 43018, which is the serial number of the date (the number of days since 1/1/1900). Then format C2 as a date, and you'll get 10/10/17.

 

[steve400243]

Thank you Mark, I new it would be a substitute formula, but could not get it. I really appreciate your help.

 

[steve400243]

Thank you Jon, never used the =MID formula for anything before. I appreciate the formulas. Have a nice rest of your day!

 

[Scott R]

I came up with
=DATEVALUE(MID(A2, FIND("on ", A2) + 3, 999))

 

[steve400243]

Another great solution, Thank you Scott R. Appreciate it!

I came up with
=DATEVALUE(MID(A2, FIND("on ", A2) + 3, 999))

 

[Rick Rothstein]

This formula should also work (format the cell as a Date)...

=0+TRIM(RIGHT(A2,8))

 

[steve400243]

Thank you Rick, Appreciate the reply!

This formula should also work (format the cell as a Date)...

=0+TRIM(RIGHT(A2,8))