What is in my cell????

[amoverton2]

Hi!

I think I'm in the right place with this post, if I need a new one please let me know... My problem is that I'm not as smart with Excel as most of you are.

Here's my problem, in the below, all of the dates have something before and after the date, if I manually go into the cell and delete what's before and after the cell, after hitting enter, it allows for new formatting of the date into what I want. I have a bunch of sheets with dates just like this and it would be painful to do it manually. I'm looking for a VBA code or formula that would clear out the hidden items within the cell that I don't know how to eliminate besides what I described above. Ideally, a code that runs after workbook activation that checks all the sheets (say for now 30 sheets with the A3:E11 cell range) removes the characters, and formats the cell to have a date formation of DD-MMM-YYYY. Clean and trim do not do the trick, and a couple of the VBA codes (CleanTrim, SmartTrim) I found while trying to figure this out haven't worked, Please Help!

DATES.xlsx
	A	B	C	D	E
1	DATE1	DATE2	DATE3	DATE4	DATE5
2	10/01/2021	10/2024	11/22/2026	11/22/2026
3	03/01/2023	03/2026	06/16/2027	06/16/2027
4	05/01/2020	11/2023	04/26/2027	04/26/2027	04/22/2024
5	11/01/2020	01/2024	06/19/2029	06/19/2029	03/03/2024
6	04/01/2021	04/2025	02/10/2024	02/10/2026
7	10/01/2020	11/2023	07/11/2029	07/11/2029	12/30/2023
8	04/01/2022	09/2025	06/09/2025	06/09/2025
9			07/06/2026	07/06/2028	08/28/2023
10	04/01/2023	03/2026	07/04/2026	07/04/2026
RJN_CTT

Thanks,
Adam

 

[Anonymous1378]

Trim does work, so it appears to be regular spaces. You just need to convert the text to a number for it to be recognized by Excel as a date. However, as you may see below, your dates are not in a consistent dd/mm/yyyy format (also my regional setting). It's possible to swap the day and month for instances where it is clearly not a month (i.e. value greater than 12), but what about instances where both the day and the month are numbers less than 12? Do you have some means to tell whether 05/04/2023 should be 05 Apr 2023 or 04 May 2023?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	DATE1	DATE2	DATE3	DATE4	DATE5
2	10/01/2021	10/2024	11/22/2026	11/22/2026			10-Jan-21	01-Oct-24	#VALUE!	#VALUE!
3	03/01/2023	03/2026	06/16/2027	06/16/2027			03-Jan-23	01-Mar-26	#VALUE!	#VALUE!
4	05/01/2020	11/2023	04/26/2027	04/26/2027	04/22/2024		05-Jan-20	01-Nov-23	#VALUE!	#VALUE!	#VALUE!
5	11/01/2020	01/2024	06/19/2029	06/19/2029	03/03/2024		11-Jan-20	01-Jan-24	#VALUE!	#VALUE!	03-Mar-24
6	04/01/2021	04/2025	02/10/2024	02/10/2026			04-Jan-21	01-Apr-25	02-Oct-24	02-Oct-26
7	10/01/2020	11/2023	07/11/2029	07/11/2029	12/30/2023		10-Jan-20	01-Nov-23	07-Nov-29	07-Nov-29	#VALUE!
8	04/01/2022	09/2025	06/09/2025	06/09/2025			04-Jan-22	01-Sep-25	06-Sep-25	06-Sep-25
9			07/06/2026	07/06/2028	08/28/2023				07-Jun-26	07-Jun-28	#VALUE!
10	04/01/2023	03/2026	07/04/2026	07/04/2026			04-Jan-23	01-Mar-26	07-Apr-26	07-Apr-26
Sheet1

Cell Formulas
Range	Formula
G2:K10	G2	=IF(A2="","",--TRIM(A2))

 

[Peter_SSs]

Try this with a copy of your workbook.

Sub TidyDates()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    With ws.Range("A:E")
      .Replace What:=" ", Replacement:="", LookAt:=xlPart
      .NumberFormat = "DD-MMM-YYYY"
      .Columns.AutoFit
    End With
  Next ws
End Sub

 

[amoverton2]

Trim does work, so it appears to be regular spaces. You just need to convert the text to a number for it to be recognized by Excel as a date. However, as you may see below, your dates are not in a consistent dd/mm/yyyy format (also my regional setting). It's possible to swap the day and month for instances where it is clearly not a month (i.e. value greater than 12), but what about instances where both the day and the month are numbers less than 12? Do you have some means to tell whether 05/04/2023 should be 05 Apr 2023 or 04 May 2023?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	DATE1	DATE2	DATE3	DATE4	DATE5
2	10/01/2021	10/2024	11/22/2026	11/22/2026			10-Jan-21	01-Oct-24	#VALUE!	#VALUE!
3	03/01/2023	03/2026	06/16/2027	06/16/2027			03-Jan-23	01-Mar-26	#VALUE!	#VALUE!
4	05/01/2020	11/2023	04/26/2027	04/26/2027	04/22/2024		05-Jan-20	01-Nov-23	#VALUE!	#VALUE!	#VALUE!
5	11/01/2020	01/2024	06/19/2029	06/19/2029	03/03/2024		11-Jan-20	01-Jan-24	#VALUE!	#VALUE!	03-Mar-24
6	04/01/2021	04/2025	02/10/2024	02/10/2026			04-Jan-21	01-Apr-25	02-Oct-24	02-Oct-26
7	10/01/2020	11/2023	07/11/2029	07/11/2029	12/30/2023		10-Jan-20	01-Nov-23	07-Nov-29	07-Nov-29	#VALUE!
8	04/01/2022	09/2025	06/09/2025	06/09/2025			04-Jan-22	01-Sep-25	06-Sep-25	06-Sep-25
9			07/06/2026	07/06/2028	08/28/2023				07-Jun-26	07-Jun-28	#VALUE!
10	04/01/2023	03/2026	07/04/2026	07/04/2026			04-Jan-23	01-Mar-26	07-Apr-26	07-Apr-26
Sheet1

Cell Formulas
Range	Formula
G2:K10	G2	=IF(A2="","",--TRIM(A2))

I inputted the trim formula you have and it returns #value! for every single cell. I'm using 2016 excel BTW

 

[Peter_SSs]

I inputted the trim formula you have and it returns #value!

In that case I don't think that my vba suggestion will work either.
It seems that the data coming via XL2BB may not be a true reflection of the original data.
Could you upload a sample file with a couple of sheets with those tables to DropBox or OneDrive or Google Drive etc and provide a public shared link here so we can take a look at the data in its original form?

 

[amoverton2]

Try this with a copy of your workbook.

Sub TidyDates()
  Dim ws As Worksheet
 
  For Each ws In Worksheets
    With ws.Range("A:E")
      .Replace What:=" ", Replacement:="", LookAt:=xlPart
      .NumberFormat = "DD-MMM-YYYY"
      .Columns.AutoFit
    End With
  Next ws
End Sub

Unfortunately, that did not work either, there is still something on either side of the date.

 

[amoverton2]

Here's the link: Test.xlsb

 

[Peter_SSs]

Here's the link: Test.xlsb

Thanks. Try this instead (still in a copy of the workbook).

Sub TidyDates_v2()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    With ws.Range("AA:AD")
      .Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
      .NumberFormat = "DD-MMM-YYYY"
      .Columns.AutoFit
    End With
  Next ws
End Sub

 

[amoverton2]

Thanks. Try this instead (still in a copy of the workbook).

Sub TidyDates_v2()
  Dim ws As Worksheet
 
  For Each ws In Worksheets
    With ws.Range("AA:AD")
      .Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
      .NumberFormat = "DD-MMM-YYYY"
      .Columns.AutoFit
    End With
  Next ws
End Sub

That's works, TY! I have a follow up question, since that code checks all sheets, how do I make the code run on specific sheets (right now there are 27 sheets that have dates like the test file), since the actual spreadsheet is about 75 sheets and counting but it only needs to check one's with dates on them?

 

[Peter_SSs]

That's works, TY!

Good news! You are welcome.

since that code checks all sheets, how do I make the code run on specific sheets (right now there are 27 sheets that have dates like the test file), since the actual spreadsheet is about 75 sheets and counting but it only needs to check one's with dates on them?

Is there some logical way to to determine which sheets to act on or which sheets to not act on?
For example, perhaps ..

• all of the sheets to act on have something common/similar in the sheet name that does not occur with the others?
• all of the sheets in one of the groups have something similar in, say, cell G1 that the other group does not? (or like your sample cell AA30 has a "/" in it that the other sheets do not)
• the first 10 sheets do not get actioned but the rest do?

We just need some logical way to know or to test whether a particular sheet gets actioned or not.