Excel macro to add /'s to a cell value.

[Robert Franks]

I'm trying to get 3 /'s in an existing cell value. The value for example
would be 01232003 and I want the macro to insert /'s so it turns out to
be 01/23/2003.

What I've been experimenting with is this:

n = "0"
For Each cell In Selection
cell.Value = Left(cell.Value, 2) & "/" & Left(cell.Value, 2) & "/" &
Left(cell.Value, 4)


But this just results in 11/11/1122. It seems to be starting over from
the far left side with each "Left(cell.Value, *).

Any ideas?

Thanks!
RF

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[mangesh_yadav]

you might rather want to use

cell.Value = "=date(" & Right(cell.Value, 4) & "," & Left(cell.Value
2) & "," &
mid(cell.Value, 3, 2) & ")"

to convert into date,

or if you want to use your method, it should be
mid(cell.Value, 3, 2) & "/" & Left(cell.Value, 2) & "/"
Right(cell.Value, 4)

or as you require

maybe you might want to put a ' before the entire text to retain th
way you want to use it.

- Manges

 

[Alan Perkins]

You could try just selecting the range that you want to change, and use:
1) Data | Text to Columns
2) Select either Delimited or Fixed Width, it makes no difference
3) Hit "Next" then "Next" again
4) On the last screen, select the "Date" option, and the date format of your
choice
5) Hit "Finish"

Or, if you really do need a macro (this works for UK date format -
dd/mm/yyyy):-
Selection.TextToColumns _
Destination:=Selection, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4) ' note for US dates, use 0,3 here


HTH

Alan P