Replace text in a specfic cell

[Gregfox]

I would like to replace some text in a specfic cell, the cell contains thr following;
'As of Sep 4 2018'

I would like to replace 'As of' with blank that is I want to remove 'As of'

using Excel 2007 on a PC

 

[Nine Zero]

try using SUBSTITUTE

Assuming your cell is A5

=SUBSTITUTE(A5,"As of ","")

 

[Joe4]

If you want to do it in the cell itself (and not in another column), just use Excel's built-in Find/Replace functionality replacing "As of " with nothing.

 

[Gregfox]

Sorry, I forgot to add "I'm using VBA"

 

[Joe4]

Sorry, I forgot to add "I'm using VBA"

Turn on your Macro Recorder and record yourself using the Find/Replace functionality I mentioned above, and you will have VBA code that does that.

 

[Fluff]

If it's just one cell try

Range("A8").Value = Split(Range("A8").Value, "of")(1)

or

Range("A8").Value = CDate(Split(Range("A8").Value, "of")(1))

to convert to a date

 

[Nine Zero]

If it's just one cell try

Range("A8").Value = Split(Range("A8").Value, "of")(1)

or

Range("A8").Value = CDate(Split(Range("A8").Value, "of")(1))

to convert to a date

Sorry for this being irrelevant to OP question but ....

Does Split put items into an array of some sort? and is the (1) calling the second index of that array which in this case would be the date?

 

[Gregfox]

Thank you.

 

[Fluff]

That's right

 

[Rick Rothstein]

Sorry for this being irrelevant to OP question but ....

Does Split put items into an array of some sort? and is the (1) calling the second index of that array which in this case would be the date?

Yes, that is correct. Just so you know, the Split function always produces a zero-based array (meaning first element's index number is 0) even if the Option Base 1 setting is used.