Formula to extract values in cell before and after a /

[rob51852]

Hi guys

I have a spreadsheet with various cells in column A that contain data in the following format:

181/2 (18.3)

Are there some formulas that will copy the number before the / and the number immediately after (ignoring the number in brackets)?

I would like to copy the number before the / into column G and the number after the / into column H.

Thanks

 

[sandy666]

Text to Columns should work

 

[Peter_SSs]

Like this?

Excel 2016
	A	B	C	D	E	F	G	H
1	181/2 (18.3)						181	2
TTC

Cell Formulas
Range	Formula
G1	=LEFT(A1,FIND("/",A1)-1)+0
H1	=REPLACE(LEFT(A1,FIND(" ",A1)),1,FIND("/",A1),"")+0

 

[rob51852]

Hi Peter,

Thanks. The first formula works perfectly but I just get a #VALUE ! error with the second.

Am I doing something wrong?

Like this?

Excel 2016

	A	B	C	D	E	F	G	H
1	181/2 (18.3)						181	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

TTC​

Worksheet Formulas Cell Formula G1 =LEFT(A1,FIND("/",A1)-1)+0 H1 =REPLACE(LEFT(A1,FIND(" ",A1)),1,FIND("/",A1),"")+0 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[mohadin]

Hi
Try this code

Sub test()
    Dim a As Variant, i, m
    a = Cells(1, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row)
    ReDim Preserve a(1 To UBound(a), 1 To 2)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(\d+\.+\d+)|(\d+)|\(\d+.+"
        For i = 1 To UBound(a)
        Set m = .Execute(a(i, 1))
            a(i, 1) = m(0): a(i, 2) = m(1)
        Next
    End With
    Cells(1, 1).Offset(, 6).Resize(UBound(a), 2) = a
End Sub

It takes care if there is extra spaces any where

 

[sandy666]

did you try text2columns from post#2?

 

[Peter_SSs]

Hi Peter,

Thanks. The first formula works perfectly but I just get a #VALUE ! error with the second.

Am I doing something wrong?

You are most probably not doing anything wrong.

Either there is no space in the text or that space is not a 'normal' space character.
In that example, the space is the 6th character. What does this formula return if you place it in a vacant cell and point it at that sample value?

=CODE(MID(A2,6,1))

Visually, is there always a space before the "("?

 

[rob51852]

You are most probably not doing anything wrong.

Either there is no space in the text or that space is not a 'normal' space character.
In that example, the space is the 6th character. What does this formula return if you place it in a vacant cell and point it at that sample value?

=CODE(MID(A2,6,1))

Visually, is there always a space before the "("?

It returned 160 which seems odd. Visually there is always a space,

Here's the sheet if it's of use: https://drive.google.com/file/d/1KOfJppNDvsIzQwS5p_zktL8wy7zxiu1o/view?usp=sharing

 

[Peter_SSs]

It returned 160 which seems odd. Visually there is always a space,

Is the data copied from the internet? That often includes CHAR(160) for spaces.
I didn't look at your sample file but if there is always a 'space' of some sort, try this as the 2nd formula.

=REPLACE(LEFT(A1,FIND("(",A1)-2),1,FIND("/",A1),"")+0

 

[sandy666]

just for fun with Power Query aka Get&Transform

Final						Final.1	Final2
27.05. 14:30
Chennai Super Kings
Sunrisers Hyderabad
181/2 (18.3)						181​	2​
178/6 (20)						178​	6​
RR 9.78
RR 8.90
Chennai Super Kings won by 8 wickets.
Semi-finals
25.05. 14:30
Kolkata Knight Riders
Sunrisers Hyderabad
161/9 (20)						161​	9​
174/7 (20)						174​	7​
RR 8.05
RR 8.70
Sunrisers Hyderabad won by 13 runs.
Quarter-finals
23.05. 14:30
Kolkata Knight Riders
Rajasthan Royals
169/7 (20)						169​	7​
144/4 (20)						144​	4​
RR 8.45
RR 7.20
Kolkata Knight Riders won by 25 runs.
22.05. 14:30
Sunrisers Hyderabad
Chennai Super Kings
139/7 (20)						139​	7​
140/8 (19.1)						140​	8​
RR 6.95
RR 7.30
Chennai Super Kings won by 2 wickets.
INDIAIPL
Standings
20.05. 15:30
Chennai Super Kings
Kings XI Punjab
159/5 (19.1)						159​	5​
153/10 (19.4)						153​	10​
RR 8.30
RR 7.78
Chennai Super Kings won by 5 wickets.
20.05. 11:30
Delhi Capitals
Mumbai Indians
174/4 (20)						174​	4​
163/10 (19.3)						163​	10​
RR 8.70
RR 8.36
Delhi Capitals won by 11 runs.
19.05. 15:30
Sunrisers Hyderabad
Kolkata Knight Riders
172/9 (20)						172​	9​
173/5 (19.4)						173​	5​
RR 8.60
RR 8.80
Kolkata Knight Riders won by 5 wickets.
19.05. 11:30
Rajasthan Royals
Royal Challengers Bangalore
164/5 (20)						164​	5​
134/10 (19.2)						134​	10​
RR 8.20
RR 6.93
Rajasthan Royals won by 30 runs.
18.05. 15:30
Delhi Capitals
Chennai Super Kings
162/5 (20)						162​	5​
128/6 (20)						128​	6​
RR 8.10
RR 6.40
Delhi Capitals won by 34 runs.
17.05. 15:30
Royal Challengers Bangalore
Sunrisers Hyderabad
218/6 (20)						218​	6​
204/3 (20)						204​	3​
RR 10.90
RR 10.20
Royal Challengers Bangalore won by 14 runs.
16.05. 15:30
Mumbai Indians
Kings XI Punjab
186/8 (20)						186​	8​
183/5 (20)						183​	5​
RR 9.30
RR 9.15
Mumbai Indians won by 3 runs.
15.05. 15:30
Kolkata Knight Riders
Rajasthan Royals
145/4 (18)						145​	4​
142/10 (19)						142​	10​
RR 8.06
RR 7.47
Kolkata Knight Riders won by 6 wickets.
14.05. 15:30
Kings XI Punjab
Royal Challengers Bangalore
88/10 (15.1)						88​	10​
92/0 (8.1)						92​	0​
RR 5.80
RR 11.27
Royal Challengers Bangalore won by 10 wickets.
13.05. 15:30
Mumbai Indians
Rajasthan Royals
168/6 (20)						168​	6​
171/3 (18)						171​	3​
RR 8.40
RR 9.50
Rajasthan Royals won by 7 wickets.
13.05. 11:30
Chennai Super Kings
Sunrisers Hyderabad
180/2 (19)						180​	2​
179/4 (20)						179​	4​
RR 9.47
RR 8.95
Chennai Super Kings won by 8 wickets.
12.05. 15:30
Delhi Capitals
Royal Challengers Bangalore
181/4 (20)						181​	4​
187/5 (19)						187​	5​
RR 9.05
RR 9.84
Royal Challengers Bangalore won by 5 wickets.
12.05. 11:30
Kings XI Punjab
Kolkata Knight Riders
214/8 (20)						214​	8​
245/6 (20)						245​	6​
RR 10.70
RR 12.25
Kolkata Knight Riders won by 31 runs.
11.05. 15:30
Rajasthan Royals
Chennai Super Kings
177/6 (19.5)						177​	6​
176/4 (20)						176​	4​
RR 8.92
RR 8.80
Rajasthan Royals won by 4 wickets.
10.05. 15:30
Delhi Capitals
Sunrisers Hyderabad
187/5 (20)						187​	5​
191/1 (18.5)						191​	1​
RR 9.35
RR 10.14
Sunrisers Hyderabad won by 9 wickets.
09.05. 15:30
Kolkata Knight Riders
Mumbai Indians
108/10 (18.1)						108​	10​
210/6 (20)						210​	6​
RR 5.94
RR 10.50
Mumbai Indians won by 102 runs.
08.05. 15:30
Rajasthan Royals
Kings XI Punjab
158/8 (20)						158​	8​
143/7 (20)						143​	7​
RR 7.90
RR 7.15
Rajasthan Royals won by 15 runs.
07.05. 15:30
Sunrisers Hyderabad
Royal Challengers Bangalore
146/10 (20)						146​	10​
141/6 (20)						141​	6​
RR 7.30
RR 7.05
Sunrisers Hyderabad won by 5 runs.
06.05. 15:30
Kings XI Punjab
Rajasthan Royals
155/4 (18.4)						155​	4​
152/9 (20)						152​	9​
RR 8.30
RR 7.60
Kings XI Punjab won by 6 wickets.
06.05. 11:30
Mumbai Indians
Kolkata Knight Riders
181/4 (20)						181​	4​
168/6 (20)						168​	6​
RR 9.05
RR 8.40
Mumbai Indians won by 13 runs.
05.05. 15:30
Sunrisers Hyderabad
Delhi Capitals
164/3 (19.5)						164​	3​
163/5 (20)						163​	5​
RR 8.27
RR 8.15
Sunrisers Hyderabad won by 7 wickets.
05.05. 11:30
Chennai Super Kings
Royal Challengers Bangalore
128/4 (18)						128​	4​
127/9 (20)						127​	9​
RR 7.11
RR 6.35
Chennai Super Kings won by 6 wickets.
04.05. 15:30
Kings XI Punjab
Mumbai Indians
174/6 (20)						174​	6​
176/4 (19)						176​	4​
RR 8.70
RR 9.26
Mumbai Indians won by 6 wickets.
03.05. 15:30
Kolkata Knight Riders
Chennai Super Kings
180/4 (17.4)						180​	4​
177/5 (20)						177​	5​
RR 10.19
RR 8.85
Kolkata Knight Riders won by 6 wickets.
02.05. 15:30
Delhi Capitals
Rajasthan Royals
196/6 (17.1)						196​	6​
146/5 (12)						146​	5​
RR 11.42
RR 12.17
Delhi Capitals won by 4 runs (D/L method).
01.05. 15:30
Royal Challengers Bangalore
Mumbai Indians
167/7 (20)						167​	7​
153/7 (20)						153​	7​
RR 8.35
RR 7.65
Royal Challengers Bangalore won by 14 runs.
30.04. 15:30
Chennai Super Kings
Delhi Capitals
211/4 (20)						211​	4​
198/5 (20)						198​	5​
RR 10.55
RR 9.90
Chennai Super Kings won by 13 runs.
29.04. 15:30
Royal Challengers Bangalore
Kolkata Knight Riders
175/4 (20)						175​	4​
176/4 (19.1)						176​	4​
RR 8.75
RR 9.18
Kolkata Knight Riders won by 6 wickets.
29.04. 11:30
Rajasthan Royals
Sunrisers Hyderabad
140/6 (20)						140​	6​
151/7 (20)						151​	7​
RR 7.00
RR 7.55
Sunrisers Hyderabad won by 11 runs.
28.04. 15:30
Chennai Super Kings
Mumbai Indians
169/5 (20)						169​	5​
170/2 (19.4)						170​	2​
RR 8.45
RR 8.64
Mumbai Indians won by 8 wickets.
27.04. 15:30
Delhi Capitals
Kolkata Knight Riders
219/4 (20)						219​	4​
164/9 (20)						164​	9​
RR 10.95
RR 8.20
Delhi Capitals won by 55 runs.
26.04. 15:30
Sunrisers Hyderabad
Kings XI Punjab
132/6 (20)						132​	6​
119/10 (19.2)						119​	10​
RR 6.60
RR 6.16
Sunrisers Hyderabad won by 13 runs.
25.04. 15:30
Royal Challengers Bangalore
Chennai Super Kings
205/8 (20)						205​	8​
207/5 (19.4)						207​	5​
RR 10.25
RR 10.53
Chennai Super Kings won by 5 wickets.
24.04. 15:30
Mumbai Indians
Sunrisers Hyderabad
87/10 (18.5)						87​	10​
118/10 (18.4)						118​	10​
RR 4.62
RR 6.32
Sunrisers Hyderabad won by 31 runs.
23.04. 15:30
Kings XI Punjab
Delhi Capitals
143/8 (20)						143​	8​
139/8 (20)						139​	8​
RR 7.15
RR 6.95
Kings XI Punjab won by 4 runs.
22.04. 15:30
Rajasthan Royals
Mumbai Indians
168/7 (19.4)						168​	7​
167/7 (20)						167​	7​
RR 8.54
RR 8.35
Rajasthan Royals won by 3 wickets.
22.04. 11:30
Sunrisers Hyderabad
Chennai Super Kings
178/6 (20)						178​	6​
182/3 (20)						182​	3​
RR 8.90
RR 9.10
Chennai Super Kings won by 4 runs.
21.04. 15:30
Delhi Capitals
Royal Challengers Bangalore
174/5 (20)						174​	5​
176/4 (18)						176​	4​
RR 8.70
RR 9.78
Royal Challengers Bangalore won by 6 wickets.
21.04. 11:30
Kolkata Knight Riders
Kings XI Punjab
191/7 (20)						191​	7​
126/1 (11.1)						126​	1​
RR 9.55
RR 11.28
Kings XI Punjab won by 9 wickets (D/L method).
20.04. 15:30
Chennai Super Kings
Rajasthan Royals
204/5 (20)						204​	5​
140/10 (18.3)						140​	10​
RR 10.20
RR 7.57
Chennai Super Kings won by 64 runs.
19.04. 15:30
Kings XI Punjab
Sunrisers Hyderabad
193/3 (20)						193​	3​
178/4 (20)						178​	4​
RR 9.65
RR 8.90
Kings XI Punjab won by 15 runs.
18.04. 15:30
Rajasthan Royals
Kolkata Knight Riders
160/8 (20)						160​	8​
163/3 (18.5)						163​	3​
RR 8.00
RR 8.65
Kolkata Knight Riders won by 7 wickets.
17.04. 15:30
Mumbai Indians
Royal Challengers Bangalore
213/6 (20)						213​	6​
167/8 (20)						167​	8​
RR 10.65
RR 8.35
Mumbai Indians won by 46 runs.
16.04. 15:30
Kolkata Knight Riders
Delhi Capitals
200/9 (20)						200​	9​
129/10 (14.2)						129​	10​
RR 10.00
RR 9.00
Kolkata Knight Riders won by 71 runs.
15.04. 15:30
Kings XI Punjab
Chennai Super Kings
197/7 (20)						197​	7​
193/5 (20)						193​	5​
RR 9.85
RR 9.65
Kings XI Punjab won by 4 runs.
15.04. 11:30
Royal Challengers Bangalore
Rajasthan Royals
198/6 (20)						198​	6​
217/4 (20)						217​	4​
RR 9.90
RR 10.85
Rajasthan Royals won by 19 runs.
14.04. 15:30
Kolkata Knight Riders
Sunrisers Hyderabad
138/8 (20)						138​	8​
139/5 (19)						139​	5​
RR 6.90
RR 7.32
Sunrisers Hyderabad won by 5 wickets.
14.04. 11:30
Mumbai Indians
Delhi Capitals
194/7 (20)						194​	7​
195/3 (20)						195​	3​
RR 9.70
RR 9.75
Delhi Capitals won by 7 wickets.
13.04. 15:30
Royal Challengers Bangalore
Kings XI Punjab
159/6 (19.3)						159​	6​
155/10 (19.2)						155​	10​
RR 8.15
RR 8.02
Royal Challengers Bangalore won by 4 wickets.
12.04. 15:30
Sunrisers Hyderabad
Mumbai Indians
151/9 (20)						151​	9​
147/8 (20)						147​	8​
RR 7.55
RR 7.35
Sunrisers Hyderabad won by 1 wicket.
11.04. 15:30
Rajasthan Royals
Delhi Capitals
153/5 (17.5)						153​	5​
60/4 (6)						60​	4​
RR 8.58
RR 10.00
Rajasthan Royals won by 10 runs (D/L method).
10.04. 15:30
Chennai Super Kings
Kolkata Knight Riders
205/5 (19.5)						205​	5​
202/6 (20)						202​	6​
RR 10.34
RR 10.10
Chennai Super Kings won by 5 wickets.
09.04. 15:30
Sunrisers Hyderabad
Rajasthan Royals
127/1 (15.5)						127​	1​
125/9 (20)						125​	9​
RR 8.02
RR 6.25
Sunrisers Hyderabad won by 9 wickets.
08.04. 15:30
Kolkata Knight Riders
Royal Challengers Bangalore
177/6 (18.5)						177​	6​
176/7 (20)						176​	7​
RR 9.40
RR 8.80
Kolkata Knight Riders won by 4 wickets.
08.04. 11:30
Delhi Capitals
Kings XI Punjab
166/7 (20)						166​	7​
167/4 (18.5)						167​	4​
RR 8.30
RR 8.87
Kings XI Punjab won by 6 wickets.
07.04. 15:30
Mumbai Indians
Chennai Super Kings
165/4 (20)						165​	4​
169/9 (19.5)						169​	9​
RR 8.25
RR 8.52
Chennai Super Kings won by 1 wickets.

[SIZE=1]// Table1
let
    Split = Table.SplitColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Final", Splitter.SplitTextByAnyDelimiter({"/"," ","("}, QuoteStyle.Csv)),
    Type = Table.TransformColumnTypes(Table.SelectColumns(Split,{"Final.1", "Final.2"}),{{"Final.1", Int64.Type}, {"Final.2", Int64.Type}}),
    RC = Table.RemoveColumns(Table.AddColumn(Table.ReplaceErrorValues(Type, {{"Final.1", null}, {"Final.2", null}}), "Final2", each if [Final.1] = null then [Final.1] else [Final.2]),{"Final.2"})
in
    RC[/SIZE]

edit: this is NOT vba!