Formulas to return numbers from start and end of a string

[Motox]

Hi,

I receive a spreadsheet from another department that has strings within cells of the format:

5 ---- 56

34 ----- 67

99 ----- 125

The first number in the string is a score and the last number is the total. I realise this is a terrible way to enter data into Excel but there is no chance that it will be changed.

I want to have a formula that returns the number at the start of the string and another formula that shows the number at the end of the string.


There is usually a gap between numbers and the hyphens, but not always.


The number of hyphens used is not always the same.


The numbers returned must be in a format that I can use in calculations (ie, numbers not text).


Is there a pair of formulas that can do this?


Thanks very much

Dave

[AliGW]

Just to be clear - there are blank cells at either side of these strings - yes?

[BadlySpelledBuoy]

Attached is one method that appears to work for your small sample data.
It makes the assumption that the values at either end of the string will never be more than half the total string length.

BSB

[teylyn]

In Excel 2013 and higher, this can easily be done with Flash Fill. Manually type the value to be extracted in the cell to the right. Start typing the value for the next row and Excel will take an intelligent guess at what you want to achieve.

2016-08-11_18-46-49.gif

If Flash Fill is not an option, I'd use Find/Replace to replace all blanks with the -, then repeatedly replace two dashes "--" with just one "-" until all numbers are separated by only one dash, then use text to columns with a dash as the delimier. Record the whole thing with the macro recorder and replay when you have new data.

Or with formulas. Data is in A2. There can be spaces before or after the dashes. The sample data has all combinations of spaces/no spaces between dash and number.

To extract the first number

=LEFT(A2,FIND("-",SUBSTITUTE(A2," ","-"))-1)+0

To extract the last number

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","-"),LEFT(SUBSTITUTE(A2," ","-"),FIND("-",SUBSTITUTE(A2," ","-"))),""),"-","")+0

2016-08-11_19-04-27.gif

cheers, teylyn

[AliGW]

NB: I believe this interpretation of the problem was wrong - please ignore!

Try these:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K
5	5	-	-	-	-	-	-	-	-	-	56
6
7				34	-		-		-	67
8
9					99						125
10
11
12
13	=LOOKUP(2,1/(A5:R5),A5:R5)	=INDEX(A5:R5, MATCH(FALSE, ISBLANK(A5:R5), 0))
14
15	=LOOKUP(2,1/(A7:R7),A7:R7)	=INDEX(A7:R7, MATCH(FALSE, ISBLANK(A7:R7), 0))
16
17	=LOOKUP(2,1/(A9:R9),A9:R9)	=INDEX(A9:R9, MATCH(FALSE, ISBLANK(A9:R9), 0))

Sheet: Sheet1

The second formula is an array formula - confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[soledad]

Hi,

I receive a spreadsheet from another department that has strings within cells of the format:

5 ---- 56

34 ----- 67

99 ----- 125

The first number in the string is a score and the last number is the total. I realise this is a terrible way to enter data into Excel but there is no chance that it will be changed.

I want to have a formula that returns the number at the start of the string and another formula that shows the number at the end of the string.


There is usually a gap between numbers and the hyphens, but not always.


The number of hyphens used is not always the same.


The numbers returned must be in a format that I can use in calculations (ie, numbers not text).


Is there a pair of formulas that can do this?


Thanks very much

Dave

assume that A1 contains the string you want to extract
the first number is :
=--LEFT(A1,MIN(IF(SUBSTITUTE(A1,{" ";"-"},"")<>A1,FIND({" ";"-"},A1)))-1)
the last is :
=--RIGHT(A1,1+LEN(A1)-MIN(IF(SUBSTITUTE(A1,{" ";"-"},"")<>A1,FIND({" ";"-"},A1)))-SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{" ";"-"},""))))
hope you well

[Phuocam]

Or try this ...

To extract the first number:

=LOOKUP(10^10,--LEFT(A2,ROW($1:$10)))

To extract the last number:

=ABS(LOOKUP(10^10,--RIGHT(A2,ROW($1:$10))))

[gaixixon]

I don't know why I can't post the formula! the proxy server block my code, so i attached it here.

[AliGW]

If you use the chevron symbols < or > , you must put spaces either side of them. Also, HTML code is blocked, but BB isn't. Please try again and post your solution within the thread and not just in your attachment.

[Tony Valko]

There is usually a gap between numbers and the hyphens, but not always.

Maybe this...

Data Range

	A	B	C
1	5 ---- 56	5	56
2	34 ----- 67	34	67
3	99 ----- 125	99	125
4	10-----10	10	10
5	------	------	------

This formula entered in B1:

=--LEFT(SUBSTITUTE(A1,"-",REPT(" ",20)),10)

This formula entered in C1:

=--RIGHT(SUBSTITUTE(A1,"-",REPT(" ",20)),10)

Select B1:C1 and copy down as needed.

[Motox]

Thanks guys,

I tried:

=LEFT(A2,FIND("-",SUBSTITUTE(A2," ","-"))-1)+0

I could only get it working by removing the "+0" at the end. It then returned the value that I wanted but I could not use it in addition or subtraction. It caused #ERROR



=--LEFT(SUBSTITUTE(A1,"-",REPT(" ",20)),10) also gives me #ERROR


These worked perfectly, and allowed me to do addition and subtraction:

=LOOKUP(10^10,--LEFT(A2,ROW($1:$10)))


=ABS(LOOKUP(10^10,--RIGHT(A2,ROW($1:$10))))

Thanks very much Phuocam!


Dave

[Tony Valko]

I tried:

=LEFT(A2,FIND("-",SUBSTITUTE(A2," ","-"))-1)+0

I could only get it working by removing the "+0" at the end. It then returned the value that I wanted but I could not use it in addition or subtraction. It caused #ERROR



=--LEFT(SUBSTITUTE(A1,"-",REPT(" ",20)),10) also gives me #ERROR

Those formulas worked using your posted samples.

Here's a sample file that demonstrates it.

[Phuocam]

You are welcome!

[Motox]

Hi again,

there are also things in some of these formulas that I have never seen before.

What does 10^10 mean?

What does -- mean?

What does $1:$10 mean?

It is great to learn so many new things.

Tony,

I am sure your solution does work and thank you for the effort you have made to help.

Probably some formatting thing on my spreadsheet, I guess.

Thanks again everybody. I always learn something from every suggestion.

Dave

[gaixixon]

Hi again,

there are also things in some of these formulas that I have never seen before.

What does 10^10 mean?

What does -- mean?

What does $1:$10 mean?

It is great to learn so many new things.

Tony,

I am sure your solution does work and thank you for the effort you have made to help.

Probably some formatting thing on my spreadsheet, I guess.

Thanks again everybody. I always learn something from every suggestion.

Dave

I must say again that Excel is very powerful, and so is Dave! I've also learnt alot from this.
@Motox:
Dave's solution can be broken down as below:
If cell A1 has value: 12--3

=LOOKUP(10^10,--LEFT(A1,ROW($1:$10)))

1.

=Row(cell_address)

will return the row number of the cell in the formula.
Example: row(a5) will return 5, row(a7) will return number 7.

=ROW($1:$10)

will return row number from 1 to 10 in an array like this: {1;2;3;4;5;6;7;8;9;10}

2.

=Left(A1, {1;2;3;4;5;6;7;8;9;10} )

will return something like: {"1";"12";"12-";"12--";"12--3";"12--3";"12--3";"12--3";"12--3";"12--3"}
3.

=--Left(A1, {1;2;3;4;5;6;7;8;9;10} )

will return: {1;12;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
4. 10^10 is the tenth power of 10 which means: 10*10*.. *10* 10(ten times..) = 10000000000
5.

=lookup(10000000000, {1;12;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!} )

It will return THE LARGEST NUMBER in array {1;12;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
so it is easy to understand that we'll get 12 as #value is not a number!

That's it.
However, Dave's solution have problem with number such as:
12-1
or 12- 1
I hope that Dave can fix this to make it perfect..

[AlKey]

Enter formula in B1 and pull it to the right to C1 and then copy down

=--TRIM(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE($A1,"-"," "))," ",REPT(" ",20)),20*COLUMNS($A:A),20))

v	A	B	C
1	5 ---- 56	5	56
2	34 ----- 67	34	67
3	99 ----- 125	99	125
4	10-----10	10	10

[gaixixon]

and here is my lengthy solution in cell B1:

=LEFT (A1 , IF(IFERROR ( SEARCH ("-",A1),100) < IFERROR (SEARCH (" ",A1),100), SEARCH ("-",A1), SEARCH(" ",A1))-1)

in cell C2:

 = RIGHT  (A1, LEN(A1)-( LEN (B1)+ LEN ( SUBSTITUTE ( SUBSTITUTE ( A1,"-","|")," ","|"))-LEN ( SUBSTITUTE (SUBSTITUTE (SUBSTITUTE(A1,"-","|")," ","|"),"|",""))))

[AliGW]

Here's another way:

=SUBSTITUTE(LEFT(A1,FIND("-",A1)-1)," ","")

=SUBSTITUTE(SUBSTITUTE(RIGHT(B8,FIND("-",B8)-1)," ",""),"-","")

[FlameRetired]

Another way. Try array-entering this in B2 and fill down.

=MID(A2,1,MATCH(TRUE,ISERROR(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0)-1)

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Then in C2 array-entered again.

=RIGHT(A2,MATCH(TRUE,ISERROR(--MID(A2,1+LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),1)),0)-1)

	A	B	C
1	Data	Left	Right
2	5 ---- 56	5	56
3	34 ----- 67	34	67
4	99 ----- 125	99	125