Multiple Decimal Summing

I have a data from "a2:a1000" column. For example in "a2" as 10.123.100.1-10.123.100.10
The decimal is not in one format its may differ location in another row. (i.e. in "a3" as
10.123.1001.1-10.123.1001.100

Can be possible to get result as per below.
if "a2" as 10.123.100.1-10.123.100.10 then i required in "b2" 10.123.100.1
which is solved with formula =LEFT(A2,FIND("-",A2)-1)

Now i required add as per below.
"c2" 10.123.100.2
"d2" 10.123.100.3
"e2" 10.123.100.4
so on upto 10.123.100.10

I have try with number of way with formula LEN, CONCATENATE, MID, RIGHT, SUBSTITUTE. But not resolve our solution.

You don't want to 'SUM' these values as they're not decimal numbers but (appear to me) to be IP ranges

What you're actually trying to do is evaluate the string and then in the case of an actual range parse the string to find a start and end point, then create each of the IP values in the string.

If I'm correct, the only way I know of to do that in Excel is to write some VBA code to analyze the string and 'decode' it into what you want.

You're in the right track with determining if it's a range:
=LEFT(A2,FIND("-",A2)-1)

For example your code could be conditional on
IF FIND("-",A2) > 0

To determine the existence of a range, then further parsing out the LAST (3rd) '.' Value from each side of the IP in the range would give you a start and end value.
Then a simple (in this case 1 and 100 are the start and end values you extracted)

It appears you are trying to create IP addresses within a range. It may make more sense to split the range into two cells, one for start value and one for end value, then calcluate values between the start and end value.
If you have to stick with parsing apart the range, however, these formulae may be helpful:
B2=LEFT($A2,FIND("x",SUBSTITUTE($A2,&quo t;.","x",3)))&MID($A2,FIND(&quot ;x",SUBSTITUTE($A2,".","x&quot ;,3))+1,FIND("-",$A2)-1-FIND("x",SUBSTITUTE($A2,".",&q uot;x&quot ;,3)))+0
C2=LEFT($A2,FIND("x",SUBSTITUTE($A2,&quo t;.",&q uot;x&quot ;,3)))&MID($A2,FIND(&quot ;x",SUBSTITUTE($A2,".","x&quot ;,3))+1,FIND("-",$A2)-1-FIND("x",SUBSTITUTE($A2,".",&q uot;x&quot ;,3)))+1
D2=LEFT($A2,FIND("x",SUBSTITUTE($A2,&quo t;.",&q uot;x&quot ;,3)))&MID($A2,FIND(&quot ;x",SUBSTITUTE($A2,".","x&quot ;,3))+1,FIND("-",$A2)-1-FIND("x",SUBSTITUTE($A2,".",&q uot;x&quot ;,3)))+2
? increment the final addend by one in each subsequent column until you reach the final value in the range
J2=LEFT($A2,FIND("x",SUBSTITUTE($A2,&quo t;.",&q uot;x&quot ;,3)))&MID($A2,FIND(&quot ;x",SUBSTITUTE($A2,".","x&quot ;,3))+1,FIND("-",$A2)-1-FIND("x",SUBSTITUTE($A2,".",&q uot;x&quot ;,3)))+9
-Gordon

Thanks for trying my suggestion. The formula worked in my test file. Unfortunately, the parser on this site messed up the code every time I posted it. For some reason, the parser does not handle the double quotes in my code correctly.

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.