0

I have strings in cells which look like this (there may be hundreds)

713.00;482.00;337.00;530.00;
71.00;32.54;37.00;5300.30;
  • There are 4 numbers, each number is followed by a semi-colon.
  • The lengths of these numbers will vary

Ideal Output

I have the formula for the first cell (Split 1) which is: 

=LEFT(B2,(FIND(";",B2,1)-1))

I just cannot figure out the formualas for the following 3 cells (Split 2, 3 and 4)

NOTE: This needs to be a formula NOT the text to columns function.

Any suggestions would be appreciated, and if more info is needed please ask! :)

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
JHair
  • 194
  • 2
  • 11

1 Answers1

3

In B1 enter:

=TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

and copy across and down:

enter image description here

To get numbers rather than text substrings, use:

=--TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

and format the results with two decimal places.

One weakness is that the number 999 is arbitrary, so this formula will fail if the length of the initial string is greater than that number. To guard against this, we can use LEN($A1) instead of 999:

=--(TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",LEN($A1))),(COLUMNS($A:A)-1)*LEN($A1)+1,LEN($A1))))
Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 1
    Clever! One weakness is that the number 999 is arbitrary, so this formula will fail if the length of the initial string is greater than that number. Instead of 999, just use `LEN($A1)`! – Jean-François Corbett Dec 03 '15 at 12:50
  • @Jean-FrançoisCorbett Excellent idea!! – Gary's Student Dec 03 '15 at 12:51
  • Also I'm pretty sure there is a limit to the length of strings that Excel functions can handle... I believe it's 2^15-1. So replacing separators with a large number of spaces will crash into that limit if there are enough elements in the input list. If you're using 999 spaces, 32 elements is enough to make it fail. If you're using `LEN($A1)`, it's more, but still quite finite. – Jean-François Corbett Dec 03 '15 at 12:55
  • It strikes me that you could use this pattern to answer this question as well: [Get parent folder path from file path using cell formula](https://stackoverflow.com/questions/5504797/get-parent-folder-path-from-file-path-using-cell-formula/5505006#5505006) – Jean-François Corbett Dec 03 '15 at 12:58