remove certain amount of characters in excel cell

[krammer]

How would I remove lets say, 4 characters from the left in an excel cell? I know how to use the replace function...but from what I know, it only removes a phrase that you specify. For example:

objExcel.Range("A5:A65536").Replace "_xF8FF_", "/"

Can anyone help?

 

[IknowMe]

Reposting in forum707 should get you an answer.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[krammer]

That's a Visual Basic forum, not VBScript...but it's ok I actually found out the answer:

 strCreationTime = Left(strCreationTime , Len(strCreationTime) - 17)
      objExcel.Cells(x,y1).Value = strCreationTime

And I had to put that in my for loop so it hits every cell...

 

[PHV]

remove lets say, 4 characters from the left
Mid(5, strYourValue)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[IknowMe]

.Range("A5:A65536").Replace "_xF8FF_", "/"

and

As is .Cells(x,y1).Value = strCreationTime

are utilizing vba functions through your Excel Obj.

BTW, your solution is removing chars from the right side of a string and then populating a cell.

I misunderstood your intention. I thought you wanted to modify cells in a range already populated by trimming the first four chars of each cell.

I'm not claiming to know the best way of doing this but the folks in the forum I suggested could certainly have helped you out. Possibly even through some vba function/filter that wouldn't require a loop.

I just saw your question was going unanswered here and offering an alternative. Regardless, glad you got it figured out.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[krammer]

Thanks...that works too. So now that I've cut down the string that I have, I have to add a couple characters to it at specific lengths of the string...It's the date.

So this:

20010928

Needs to look like this:

2001-09-28

Is there an insert function for a string?

 

[krammer]

MrMilson - That was my original intention, but I saw that doing it before all the values were in place seemed easier...or at least that was the first solution that I saw lol.

 

[IknowMe]

if your using todays date:

strDate = Year(Date) & "-" & Right("0" & Month(Date),2) & "-" & Right("0" & Day(Date),2)

I learned that padding from none other than PHV

Or

strDate = "20010928"

strDate = left(strDate,4) & "-" & mid(strDate,6,2) & "-" & right (strDate,2)

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[IknowMe]

ooops

strDate = left(strDate,4) & "-" & mid(strDate,5,2) & "-" & right (strDate,2)

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[krammer]

That works great! Thank you.