Pulling String

[Guest]

sorry hit the wrong button.

I'm pulling a part of a string from one cell, and putting it in another.
I've got the method working, except one problem. In some instances the string
that's being pulled is all numbers. In these cases it's getting put into the
new cell as a date. I've tried applying CStr() to the line, but it still
pulls in the number as a date. How can I go about stopping it from doing
this. Not not all strings getting pulled are numbers, only a handful.

Here's the code I'm using:

lastRow = Cells(Rows.Count, "F").End(xlUp).Row 'Get the last row on the page.

For i = 2 To lastRow 'Start at 2 to ignore heard line.
If Cells(i, "F") <> "" Then
Cells(i, "E").Value = Mid(Cells(i, "F"), 1, InStr(1, Cells(i,
"F"), " "))
End If
Next i 'Incriment counter and reloop

 

[Bob Phillips]

Looks like your target cells are formatted as dates. Change to General.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Change your If statement to add a line as follows:
If Cells(i, "F") <> "" Then
Cells(i,"E").NumberFormat = "General"
Cells(i, "E").Value = Mid(Cells(i, "F"), _
1, InStr(1, Cells(i, "F"), " "))
End If

That will make sure the format is the same for each cell you post to.

 

[Guest]

How can I apply the General or text format to the cell in VBA? I know how to
do it on the excel spreadsheet by formatting the cell, but I'm creating the
column just before I run this loop. So I'd have to format it then.

 

[Bob Phillips]

columns("G").numberformat="General"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)