Extract word from cell & paste in blank cell

[honkin]

Is it possible to copy a word from multiple cells to a new location, but only if that location is blank?

Let me explain; Column C in the sheet is labelled Competition. If the selection is a football match, then the league is listed here, ie Norwegian 1st Division. If the selection is a horse race, then this cell is blank.

Column B is labelled Description and it has all of the information on the selections; Time, Date and so on.

My aim is to fill in the blanks in Column C with the name of the race course, which is located in Column B - 19:15 Windsor 5th Jul. The date and time will be variable as will the name, but I want to copy whatever the word is to the blank cell in Column C.

Examples
19:15 Windsor 5th Jul - the required word is Windsor
17:15 Pontefract 6th Jul - the required word is Pontefract

So you can see that the name of the course is always immediately after the time and finishes before the date. The ones from Australia are slightly different

03:01 EFrm (AUS) 7th Jul - the required info is EFrm (Aus)
03:29 Graf (AUS) 7th Jul - the required info is Graf (Aus)

So again you can see that the required information is always straight after the time and finishes before the date

Is what I am asking possible at all?

BFBM Results File.xlsb
	B	C
4	Description	Competition
5	13:00 Ayr 5th Jul\7f Nov Stks\Bastogne
6	17:00 Stjordals-Blink v Grorud IL\Over/Under 1.5 Goals\Under 1.5 Goals	Norwegian 1st Division
7	17:00 Asane v Jerv\Over/Under 1.5 Goals\Under 1.5 Goals	Norwegian 1st Division
8	17:00 Asane v Jerv\Over/Under 1.5 Goals\Under 1.5 Goals	Norwegian 1st Division
9	17:00 Sogndal v Strommen\Match Odds\Strommen	Norwegian 1st Division
10	19:15 Windsor 5th Jul\5f Hcap\Clarendon House
11	19:15 Windsor 5th Jul\5f Hcap\Clarendon House
12	20:15 Grotta v Vikingur Olafsvik\Over/Under 1.5 Goals\Under 1.5 Goals	Icelandic 1 Deild
13	20:15 Vikingur Reykjavik v IA Akranes\Over/Under 1.5 Goals\Under 1.5 Goals	Icelandic Urvalsdeild
14	20:15 Vikingur Reykjavik v IA Akranes\Over/Under 1.5 Goals\Under 1.5 Goals	Icelandic Urvalsdeild
15	20:15 Vikingur Reykjavik v IA Akranes\Over/Under 1.5 Goals\Under 1.5 Goals	Icelandic Urvalsdeild
16	20:15 Vikingur Reykjavik v IA Akranes\Over/Under 0.5 Goals\Under 0.5 Goals	Icelandic Urvalsdeild
17	17:15 Pontefract 6th Jul\5f Hcap\Ava Go Joe
18	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
19	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\The Draw	Uruguayan Primera Division
20	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
21	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
22	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
23	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
24	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\The Draw	Uruguayan Primera Division
25	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Liverpool Montevideo	Uruguayan Primera Division
26	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
27	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\The Draw	Uruguayan Primera Division
28	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
29	23:00 Cruzeiro MG v Coritiba\Over/Under 2.5 Goals\Over 2.5 Goals	Brazilian Serie B
30	23:00 Cruzeiro MG v Coritiba\Both teams to Score?\Yes	Brazilian Serie B
31	23:30 Santos v Athletico-PR\Over/Under 2.5 Goals\Over 2.5 Goals	Brazilian Serie A
32	23:30 Santos v Athletico-PR\Both teams to Score?\Yes	Brazilian Serie A
33	23:30 Santos v Athletico-PR\Match Odds\Athletico-PR	Brazilian Serie A
34	03:01 EFrm (AUS) 7th Jul\R1 1800m Mdn\8. Victoria Park
35	03:01 EFrm (AUS) 7th Jul\R1 1800m Mdn\8. Victoria Park
36	03:29 Graf (AUS) 7th Jul\R1 1600m CL2\2. Divine Eleven
37	03:29 Graf (AUS) 7th Jul\R1 1600m CL2\9. Lord Avatar
38	03:29 Graf (AUS) 7th Jul\R1 1600m CL2\2. Divine Eleven
39	03:36 EFrm (AUS) 7th Jul\R2 1400m 2yo\5. Millward
40	03:36 EFrm (AUS) 7th Jul\R2 1400m 2yo\7. Mishani Diva
BM16

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:O1048576	Cell	contains a blank value	text	NO

cheers

 

[Peter_SSs]

Assuming those column C values are not formula results, try this with a copy of your workbook

Sub GetLocation()
  With Range("C5:C" & Range("B" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace(Replace("if(#="""",trim(replace(left(@,find(""\"",@)-8),1,5,"""")),#)", "#", .Address), "@", .Offset(, -1).Address))
  End With
End Sub

Here is my sheet after running the code

honkin.xlsm
	B	C
4	Description	Competition
5	13:00 Ayr 5th Jul\7f Nov Stks\Bastogne	Ayr
6	17:00 Stjordals-Blink v Grorud IL\Over/Under 1.5 Goals\Under 1.5 Goals	Norwegian 1st Division
7	17:00 Asane v Jerv\Over/Under 1.5 Goals\Under 1.5 Goals	Norwegian 1st Division
8	17:00 Asane v Jerv\Over/Under 1.5 Goals\Under 1.5 Goals	Norwegian 1st Division
9	17:00 Sogndal v Strommen\Match Odds\Strommen	Norwegian 1st Division
10	19:15 Windsor 5th Jul\5f Hcap\Clarendon House	Windsor
11	19:15 Windsor 5th Jul\5f Hcap\Clarendon House	Windsor
12	20:15 Grotta v Vikingur Olafsvik\Over/Under 1.5 Goals\Under 1.5 Goals	Icelandic 1 Deild
13	20:15 Vikingur Reykjavik v IA Akranes\Over/Under 1.5 Goals\Under 1.5 Goals	Icelandic Urvalsdeild
14	20:15 Vikingur Reykjavik v IA Akranes\Over/Under 1.5 Goals\Under 1.5 Goals	Icelandic Urvalsdeild
15	20:15 Vikingur Reykjavik v IA Akranes\Over/Under 1.5 Goals\Under 1.5 Goals	Icelandic Urvalsdeild
16	20:15 Vikingur Reykjavik v IA Akranes\Over/Under 0.5 Goals\Under 0.5 Goals	Icelandic Urvalsdeild
17	17:15 Pontefract 6th Jul\5f Hcap\Ava Go Joe	Pontefract
18	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
19	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\The Draw	Uruguayan Primera Division
20	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
21	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
22	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
23	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
24	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\The Draw	Uruguayan Primera Division
25	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Liverpool Montevideo	Uruguayan Primera Division
26	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
27	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\The Draw	Uruguayan Primera Division
28	19:00 Liverpool Montevideo v Wanderers (Uru)\Match Odds\Wanderers (Uru)	Uruguayan Primera Division
29	23:00 Cruzeiro MG v Coritiba\Over/Under 2.5 Goals\Over 2.5 Goals	Brazilian Serie B
30	23:00 Cruzeiro MG v Coritiba\Both teams to Score?\Yes	Brazilian Serie B
31	23:30 Santos v Athletico-PR\Over/Under 2.5 Goals\Over 2.5 Goals	Brazilian Serie A
32	23:30 Santos v Athletico-PR\Both teams to Score?\Yes	Brazilian Serie A
33	23:30 Santos v Athletico-PR\Match Odds\Athletico-PR	Brazilian Serie A
34	03:01 EFrm (AUS) 7th Jul\R1 1800m Mdn\8. Victoria Park	EFrm (AUS)
35	03:01 EFrm (AUS) 7th Jul\R1 1800m Mdn\8. Victoria Park	EFrm (AUS)
36	03:29 Graf (AUS) 7th Jul\R1 1600m CL2\2. Divine Eleven	Graf (AUS)
37	03:29 Graf (AUS) 7th Jul\R1 1600m CL2\9. Lord Avatar	Graf (AUS)
38	03:29 Graf (AUS) 7th Jul\R1 1600m CL2\2. Divine Eleven	Graf (AUS)
39	03:36 EFrm (AUS) 7th Jul\R2 1400m 2yo\5. Millward	EFrm (AUS)
40	03:36 EFrm (AUS) 7th Jul\R2 1400m 2yo\7. Mishani Diva	EFrm (AUS)
Sheet3

 

[honkin]

Hi Peter

Thanks so much for your reply.

It is almost spot on, but what happens when the date is double digit is that the first digit of the data appears after the course name, just as below

14:15 Perth 11th Jul\2m Nov Hrd\Homme Dun Soir - Perth 1
17:25 Sligo 11th Jul\2m2f INHF\Aslukwoodhavit - Sligo 1
13:15 Newton Abbot 12th Jul\2m2f Nov Hrd\Small Bad Bob - Newton Abbot 1
13:00 Market Rasen 17th Jul\2m2f Nov Hrd\Dal Horrisgle - Market Rasen 1

On the dates that are single digit, no issues. I imagine if the date was 24, then it would be using a 2 instead of a 1

So there is just something odd somewhere in the value line

cheers

 

[Peter_SSs]

Sorry, I thought that I had allowed for that but must have mis-counted. Try making this change.

.Value = Evaluate(Replace(Replace("if(#="""",trim(replace(left(@,find(""\"",@)-9),1,5,"""")),#)", "#", .Address), "@", .Offset(, -1).Address))

 

[honkin]

Sorry, I thought that I had allowed for that but must have mis-counted. Try making this change.

.Value = Evaluate(Replace(Replace("if(#="""",trim(replace(left(@,find(""\"",@)-9),1,5,"""")),#)", "#", .Address), "@", .Offset(, -1).Address))

Perfect mate and thanks so much. Just save a load of time highlighting, copying and pasting those course name

Take care

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.