Find - Copy - and Paste

[lienlee]

Hi Guys,

I require some assistance with excel 2003.

I have a column I two different informations. Ones that end with a .csv or .txt.

For example

File1.csv
File2.txt

I have 2000 + records of these and I need to take the .csv and .txt string of the cells and put them in the next column and same row it belongs too.

For example column I will be updated to
File1
File2

and column J will have
.csv.
.txt

A macro to update these records would be most helpful

Thanks for the help!

[Rayman]

try this
[vba]Sub TextInColumns()
Range("A2:A10").Select ' put here your range
Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub[/vba]

[youngmcc]

Hi Guys,

I require some assistance with excel 2003.

I have a column I two different informations. Ones that end with a .csv or .txt.

For example

File1.csv
File2.txt

I have 2000 + records of these and I need to take the .csv and .txt string of the cells and put them in the next column and same row it belongs too.

For example column I will be updated to
File1
File2

and column J will have
.csv.
.txt

A macro to update these records would be most helpful

Thanks for the help!

Hi

Please find another example.

This will only do it for ONLY .csv and .txt leaving all other data types in the cell and allows you to have fullstops in your filenames.

[vba]

Sub Test()
wsName = "Sheet1" 'Rename to appropriate Sheet
intColNum = 9 'Column I

For intRowCount = 1 To Sheets(wsName).Range("I1").CurrentRegion.Rows.Count

'Checks if the last 4 characters are CSV
If Right(Sheets(wsName).Cells(intRowCount, intColNum), 4) = ".csv" Then

avarCSVSplit = Split(Sheets(wsName).Cells(intRowCount, intColNum).Value, ".csv")

Sheets(wsName).Cells(intRowCount, intColNum) = avarCSVSplit(0)
Sheets(wsName).Cells(intRowCount, intColNum + 1) = ".csv"

'Checks if the last 4 characters are TEXT
ElseIf Right(Sheets(wsName).Cells(intRowCount, intColNum), 4) = ".txt" Then

avarTXTSplit = Split(Sheets(wsName).Cells(intRowCount, intColNum).Value, ".txt")

Sheets(wsName).Cells(intRowCount, intColNum) = avarTXTSplit(0)
Sheets(wsName).Cells(intRowCount, intColNum + 1) = ".txt"

Else

End If

Next intRowCount

End Sub

[/vba]

[lienlee]

Hi

Please find another example.

This will only do it for ONLY .csv and .txt leaving all other data types in the cell and allows you to have fullstops in your filenames.

[vba]

Sub Test()
wsName = "Sheet1" 'Rename to appropriate Sheet
intColNum = 9 'Column I

For intRowCount = 1 To Sheets(wsName).Range("I1").CurrentRegion.Rows.Count

'Checks if the last 4 characters are CSV
If Right(Sheets(wsName).Cells(intRowCount, intColNum), 4) = ".csv" Then

avarCSVSplit = Split(Sheets(wsName).Cells(intRowCount, intColNum).Value, ".csv")

Sheets(wsName).Cells(intRowCount, intColNum) = avarCSVSplit(0)
Sheets(wsName).Cells(intRowCount, intColNum + 1) = ".csv"

'Checks if the last 4 characters are TEXT
ElseIf Right(Sheets(wsName).Cells(intRowCount, intColNum), 4) = ".txt" Then

avarTXTSplit = Split(Sheets(wsName).Cells(intRowCount, intColNum).Value, ".txt")

Sheets(wsName).Cells(intRowCount, intColNum) = avarTXTSplit(0)
Sheets(wsName).Cells(intRowCount, intColNum + 1) = ".txt"

Else

End If

Next intRowCount

End Sub

[/vba]

Hi,

This works perfectly, however, how can i remove column I's .txt/csv because I have it in Column J already.

[youngmcc]

Hi,

This works perfectly, however, how can i remove column I's .txt/csv because I have it in Column J already.

Hi,

The code should already do that.

Example

Column I contains

File1.txt
File2.txt
File3.csv
File4.doc

Column I would then look like this:

File1
File2
File3
File4.doc

Column J would look like this:

.txt
.txt
.csv
(BLANK)

If you are having any difficulties, can you post an example workbook to take a look at?

Thanks

Brian

[lienlee]

Hi,

The code should already do that.

Example

Column I contains

File1.txt
File2.txt
File3.csv
File4.doc

Column I would then look like this:

File1
File2
File3
File4.doc

Column J would look like this:

.txt
.txt
.csv
(BLANK)

If you are having any difficulties, can you post an example workbook to take a look at?

Thanks

Brian

Hi Brian,

Thanks I actually got it working. I made a few changes which caused it to not delete.

Thanks for the help.
Much appreciated!