Forum Discussion
Sebastien2480
Jan 30, 2023Copper Contributor
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 cer...
dscheikey
Jan 30, 2023Bronze Contributor
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,"")
)
Sebastien2480
Jan 30, 2023Copper Contributor
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.
thanks
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.
thanks
- dscheikeyJan 30, 2023Bronze Contributor
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,"") )
- Sebastien2480Jan 30, 2023Copper ContributorThanks For your help
I ended up splitting it in 2 cell for the input. one for NS and EW
This is what i came up with that works.
=LET( ns,ABS(AI13)& " mm "&IF(VALUE(AI13)<0,"S","N"), ew,ABS(AJ13)& " mm "&IF(VALUE(AJ13)<0,"W","E"), IFERROR(ns&" & "&ew,"") )
Thanks for your help