Progressively adding part of a cell

[Doglover]

Hi, hopefully this is simple, but I haven't been able to find a thread pertaining to my problem.

I have thousands of products which have (as an example) the progression
33.0 mm, in the next cell 35.0 mm, then 37.0 mm, etc. or sometimes they have a different number in the decimal space (of course)

I tried the following formula:
=MID(F13640,1,4)+2&" "&MID(F13640,6,2)

which worked but the decimal didn't carry through, so I tried:
=MID(F13640,1,4)+2&".0 "&MID(F13640,6,2)

which worked, but it won't work with anything other than .0 after the number. Is there a way to add a value progressively to just a part of the cell, while still resulting in the correct number of digits? Thanks so much in advance!

 

[schielrn]

Welcome to the Board!

I'm not exactly sure I follow what you said. Is there anyway you can post about 5 different samples and expected resultes. Make sure you include different scenarios. Aslo youc an copy and paste the examples directly from Excel to the board. I would recommend putting borders around the data like so:

<TABLE style="WIDTH: 225pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=301><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" span=2 width=107><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=87 align=right>33.0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=107 align=right>35.0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=107 align=right>37.0</TD></TR></TBODY></TABLE>

 

[Doglover]

Hmm, copy/paste didn't work. But imagine boxes around the following numbers: so the progression is down the F column as opposed to across...

38.0 mm
40.0 mm
42.0 mm
44.0 mm
46.0 mm
etc.

another progression looks like:

10.0 mm
12.5 mm
15.0 mm
17.5 mm
20.0 mm
22.5 mm
etc

Hopefully that helps. So I'm looking for a formula so that all I need to adapt is the amount that is getting added to the progression...(2 in the first example, 1.5 in the second...)
Thanks!

 

[Doglover]

Sorry, 2.5 in the second example...

 

[schielrn]

Ok how about:

=text(MID(F13640,1,4)+2,"#.0")&" "&MID(F13640,6,2)

This assumes that all of your numbers are 4 digits including the decimal? Will that always be true? Also do they always end with mm? There may be ways to make this more dynamic.

Hope that helps.

 

[Doglover]

That's perfect!! It's not always mm, but always measurements...could be in, cm, oz, etc...they are always 4 digits though, so this should work. Thank you so much!!!

 

[jmthompson]

That seems to force the last digit to always be "0"

this is less elegant, but appears to work

=IF(RIGHT(TEXT((LEFT(A1,4)+2.5),"##.#"),1)=".",(TEXT((LEFT(A1,4)+2.5),"##.#")&"0"&RIGHT(A1,3)),(TEXT((LEFT(A1,4)+2.5),"##.#")&RIGHT(A1,3)))

 

[schielrn]

Ok if they are alwyas 4 digits then you are fine with what you have, but assuming your measurements are always 2 digits you could use:

=TEXT(left(A1,len(a1)-3)+2.5,"#.0")&right(A1,3)

Glad it worked.

 

[schielrn]

That seems to force the last digit to always be "0"

this is less elegant, but appears to work

=IF(RIGHT(TEXT((LEFT(A1,4)+2.5),"##.#"),1)=".",(TEXT((LEFT(A1,4)+2.5),"##.#")&"0"&RIGHT(A1,3)),(TEXT((LEFT(A1,4)+2.5),"##.#")&RIGHT(A1,3)))

I get decimals other than 0?

Excel Workbook
	A
10	56.5 mm
11	59.0 mm
12	61.5 mm
13	64.0 mm
Sheet2

Cell Formulas
Range	Formula
A10	=TEXT(MID(A9,1,4)+2.5,"#.0")&" "&MID(A9,6,2)
A11	=TEXT(MID(A10,1,4)+2.5,"#.0")&" "&MID(A10,6,2)
A12	=TEXT(MID(A11,1,4)+2.5,"#.0")&" "&MID(A11,6,2)
A13	=TEXT(MID(A12,1,4)+2.5,"#.0")&" "&MID(A12,6,2)

 

[jmthompson]

Sorry, I must have done something funky the first time I tested.