how can I extract the day from a cell

[ummjay]

how can i extract the day from a cell?

i.e. says: "August 13 2014"

I want to extract the day "13" , this cell constantly updates.

thanks!

 

[ummjay]

mikerickson,
not working either. says #VALUE!

 

[Scott Huish]

Does this work for you as a formula on the spreadsheet?

=DAY(SUBSTITUTE(A93," ",", ",2))

If not, are the quotation marks actually in the cell?

 

[mikerickson]

How about
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),255,255))

 

[ummjay]

Scott/Mike,
Both solutions work, thanks.

how can i add these into my VBA. I want to set that value as a string.

i.e
ReportDay = (the day given by that formula).

Also, i want to use A93, using the workbook Report.

Set Report = ActiveWorkbook
ReportDay = Day(Substitute(Report.Worksheets(1).Range("A93"), " ", ", ", 2))

or

ReportDay = TRIM(MID(SUBSTITUTE(Report.Worksheets(1).Range("A93")," ",REPT(" ",255)),255,255))

How can i do that?

thanks again.

 

[ummjay]

any ideas?

 

[Scott Huish]

ReportDay = Day(Application.WorksheetFunction.Substitute(Report.Worksheets(1).Range("A93"), " ", ", ", 2))

 

[ummjay]

that's awesome scott, that did it.

if I wanted to search a range from B8-K19, for the string ReportDay, and add a value in 2 cells below that cell containing the string, would you happen to know how to do that?

i.e I have a calendar , each cell only has the day, then i have 2 cells beneath each of them.
I want to .PasteSpecial xlPasteValuesAndNumberFormats in the 2nd cell beneath the cell where the string is located.

how can i accomplish this?

thanks!!

 

[ummjay]

any ideas scott? really stuck on this one!!