Solved: Cut data before first space

[Dave T]

Hello All,

I have been given an Excel list of photos where the person who took them has manually renamed each photo.
Instead of each photo being the default: IMG_0001.JPG they have been renamed to describe what the photo is and the description has text of varying lengths i.e. 1. Pigeon Droppings on girder.JPG or 2. General Underside.jpg.

I am after a macro that will allow me to manually select each varying range (I would prefer not to use .End(xlUp)), which when run will do the following:

Extract the number only from column B (without the full stop) to column A
Delete the number, full stop and space from column B so all that remains is the photo name
Delete the file extension part i.e. .JPG from column B so all that remains is the photo descriptionI have found some code examples that extract the number, but I am having problems with deleting the full stop.
I have not tried deleting the last four parts of the extension yet.

So combining both of these actions into the same macro would help me out greatly.

REgards,
Dave T

[Dave T]

Sorry but I meant to include an example Workbook of what I am try to achieve.

Regards,
Dave T

[lotuxel]

Please try this

Sub ExtractToFirstSpaceV1()
'http://www.vbaexpress.com/forum/showthread.php?t=41523
Dim LastRow As Long
Dim i As Long
Dim a As String

With Worksheets("Sheet1")
LastRow = .Cells(2, 2).End(xlDown).Row
For i = 2 To LastRow
a = Cells(i, 2).Value
a = Right(a, Len(a) - InStr(1, a, " "))
Cells(i, 1) = Left(a, Len(a) - 4)
Next
End With
End Sub

[Dave T]

Hello Lotuxel

Thanks for your reply, but when I ran your solution I realised I made a mistake with the workbook I had posted.

The end result I was after should be like this:
Column A | .Column B
Photo No. .|. Description
1 .............|. Photo one
2 .............|. Photo two

The first part of column B (the number is cut and pasted in column A) the remaining part in column B has the number, full stop and space/s deleted and the file extension deleted.

Thanks again for your reply and I will play with your code.

Regards,
Dave T

[lotuxel]

Dim LastRow As Long
Dim i As Long
Dim a As String
Dim n As String

With Worksheets("Sheet1")
LastRow = .Cells(2, 2).End(xlDown).Row
For i = 2 To LastRow
a = Cells(i, 2).Value
n = Left(a, InStr(1, a, ".") - 1)
a = Trim(Right(a, Len(a) - InStr(1, a, ".")))
Cells(i, 1) = n
Cells(i, 2) = Left(a, Len(a) - 4)
Next
End With
End Sub

[snb]

Use this UDF


Function F_snb_001(c00)
F_snb_001 = Trim(Split(c00, ".")(1))
End Function


In cell C2:


=F_snb_001(B2)

[Dave T]

Hello lotuxel,

Your solution works well for that very specific instance and works exactly as I hoped it would.
It leaves values in columns A and B that are eventually exported into another program.

Can you please help me to modify it further, as it keeps causing an error when I use it in the worksheet in the manner normally I would use it ?

As I said in my initial post I wanted to be able to manually select the cells that the macro will run on, and only those cells within the selection.

I have created another example spreadsheet in an attempt to replicate how it might be used (same layout).
• Row two is hidden as I use With ActiveCell.CurrentRegion to sort on an individual range of cells using the numbers in column A for the sort order. If the sequence of the numbers in Column A are changed at a later time I run the sorting macro again
• The blank row (Row 2) prevents the headers in first row being included in the sort when using With ActiveCell.CurrentRegion
• There may be one or two blank rows (to separate With ActiveCell.CurrentRegion sort) between each group of photos (each group of photos are specific to an individual structure)

My preference would be for me (refer to attached workbook) to manually be able to select cells B4 to B18 and run the macro and this and only this range is used.
Then I would select B21 to B29, then B32 to B43 and so and so on (with me determing what will be split).


Hello snb,
Thank you for your reply as well. Your very short coding never ceases to amazes me, but I need values in columns A and B as these are exported to another program.

Regards,
Dave T

[lotuxel]

Snb's function is excellent!
Please try this as according to requirement

Sub ExtractToFirstSpaceV3()

Dim rng As Range
Dim r As Range

Set rng = Selection
For Each r In rng
r.Offset(, -1) = F_snb_001(r.Value, 0)
r = F_snb_001(r.Value, 1)
Next

End Sub
Function F_snb_001(c00, n)
'http://www.vbaexpress.com/forum/showthread.php?t=46850
F_snb_001 = Trim(Split(c00, ".")(n))
End Function

[Dave T]

Wow lotuxel, what can I say...

Quite a departure from your original code, but works exactly as I wanted.
Thank you very, very much.

The combined code of yours and snb's is now harder for me to work out what was being done.

I was trying to work out how to modify your previous code to remove the LastRow = and the .End(xlDown).Row parts.
Being able to manually select a specific range using your original slighly longer code was easier to follow.

Can either of you give me some hints as to how to achieve this.

Once again, thanks to both you and snb for your help.

Regards,
Dave T

[snb]

you can easily copy and adapt my suggestion into 2 udf's:


Function F_snb_001(c00)
F_snb_001 = Trim(Split(c00, ".")(1))
End Function

Function F_snb_002(c00)
F_snb_002 = Trim(Split(c00, ".")(0))
End Function

[lotuxel]

Sub ExtractToFirstSpaceV3()
Dim Row As Long
Dim LastRow As Long
Dim i As Long
Dim a As String
Dim n As String
With Worksheets("Sheet1")
Row = Selection.Row
LastRow = Selection.Rows.Count + Row - 1
For i = Row To LastRow
a = Cells(i, 2).Value
n = Left(a, InStr(1, a, ".") - 1)
a = Trim(Right(a, Len(a) - InStr(1, a, ".")))
Cells(i, 1) = n
Cells(i, 2) = Left(a, Len(a) - 4)
Next
End With
End Sub

[Dave T]

Hello lotuxel and snb,

Fantastic and a big thank you to both of you.
Now I have a range of options to choose from.

I took snb's advice and had a go at putting both of his UDf's in the code provided by lotuxel and came up with the following code (I swapped the numbers used in snb's UFD's so F_snb_001 now refers to the first part i.e. the number and F_snb_002 is the remaining data).



Sub ExtractToFirstSpace()
'http://www.vbaexpress.com/forum/showthread.php?t=46850
Dim rng As Range
Dim r As Range
Set rng = Selection
For Each r In rng
r.Offset(, -1) = F_snb_001(r.Value)
r = F_snb_002(r.Value)
Next
End Sub

Function F_snb_001(c00)
F_snb_001 = Trim(Split(c00, ".")(0))
End Function

Function F_snb_002(c00)
F_snb_002 = Trim(Split(c00, ".")(1))
End Function


As I have never had much exposure to UDF's, I always thought they only gave a formula result. It was lotuxel's code that really changed my thinking about UDF's.

I am also wondering if there is a preferred naming convention for UDF's as snb has used Function F_snb_001(c00) however I have seen other UDF's starting like this... Function firstnumber(mystr As String) As Variant. Should the part within the brackets be more descriptive ? How would snb's UDF names vary from his use of c00 i.e. is there a better more explanatory title to use ?
Do you know of any sources where I can learn more about the naming of UDF's ?

As my initial question has been solved I will mark this post a solved and if I get any responses to my last question all well and good.

Regards,
Dave T

[lotuxel]

Hi Dave T,
There are no rule to use the letter (string) in function and procedure..
u can use any string any title as you prefer and easy to understand.
Practice makes perfect!.
:thumb

[snb]

Hear, hear