Formatting Text Data to Proper Date Format

[claybwagner28]

Hello, I downlaoded a source file which the dates are erronieuns typed in as text.
For example, Cell B4 has the following cell entry “Jan 7 2022” with the extra space, no comma and listed as general in format. Ultimately I am trying to change “Jan 7 2022” to “1/7/22”.
I tied to use TRIM but the file still reads it as text.

Any sugesstions? Thanks in advance!
Clay

 

[eastrand]

Try this. =TEXT(DATE(RIGHT(TRIM(B4),4),MONTH(DATEVALUE(LEFT(TRIM(B4),3)&" 1, 2017")),MID(TRIM(B4),5,1)),"MM/DD/YY")

 

[eastrand]

Sorry, I miss read that. Check you date format and see if it's set for a four digit year

 

[Rick Rothstein]

Does this formula work for you...

=0+REPLACE(B4,LEN(B4)-4,0,",")

 

[Fluff]

In an absence to an answer to my previous question, at a guess try

=Substitute(B4,char(160),"")+0

 

[claybwagner28]

Does this formula work for you...

=0+REPLACE(B4,LEN(B4)-4,0,",")

That worked. Thank you both for your help!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[claybwagner28]

In an absence to an answer to my previous question, at a guess try

=Substitute(B4,char(160),"")+0

Rick’s worked. Thak you for the assiatance and tips on updating my profile. Have a great day!

 

[claybwagner28]

Rick’s worked. Thak you for the assiatance and tips on updating my profile. Have a great day!

Ugh my spelling seems to be as good as my Excel skills…..(facepalm)