Jan 30 2023 09:01 AM
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
Jan 30 2023 10:01 AM
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,"")
)
Jan 30 2023 10:13 AM
Jan 30 2023 10:56 AM
Sorry! 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,"")
)
Jan 30 2023 11:09 AM