Forum Discussion
Excel Formula for numbers and letters
Hi
I have a report template that I'm trying to save time on filling out.
I have numbers that is directional, so North/South and East/West. there numbers are millimeters and need to show a certain way:
As Example Final product in one cell should Read "5mm N & 15mm W"
I want to set-up a formula for it to fill out the report
in a separate page or area. I want to be able to type North and East number as positive numbers and South and West numbers be negative. then it automatically fill out the proper cell.
as for the Example above North/South will be 5 and East/West be -15
I want to be able to combine 2 direction cells into one cell.
Thanks for any help on this
Seb
Hi, I have a solution that requires the input in a cell that was previously marked as text. If you don't do this, there is a risk that Excel will calculate your input if you enter a negative number as the W/O value.
To get the result "5 mm N & -15 mm W" you only have to enter "5 -15". This is the space character to separate the two values. You can also use any other character. Then please change the formula in TEXTSPLIT().
The following formula analyses your input and creates the correct string.=LET( in,TEXTSPLIT(A1," "), ns,INDEX(in,1,1)& " mm "&IF(VALUE(INDEX(in,1,1))<0,"S","N"), ow,INDEX(in,1,2)& " mm "&IF(VALUE(INDEX(in,1,2))<0,"W","O"), IFERROR(ns&" & "&ow,"") )- Hi
This has gotten me allot closer to what I want it to do. The only thing left is to have it get rig of the negative numbers. I also wont let me put a negative as the first number. with a space between both set of numbers. I might be able to rework the formula to have 2 cell input.
thanksSorry! I had forgotten to delete the minus signs from the result.
=LET( in,TEXTSPLIT(A1," "), ns,SUBSTITUTE(INDEX(in,1,1),"-","")& " mm "&IF(VALUE(INDEX(in,1,1))<0,"S","N"), ow,SUBSTITUTE(INDEX(in,1,2),"-","")& " mm "&IF(VALUE(INDEX(in,1,2))<0,"W","O"), IFERROR(ns&" & "&ow,"") )
