Forum Discussion

Sebastien2480's avatar
Sebastien2480
Copper Contributor
Jan 30, 2023

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...
Formulas and Functions
Sebastien2480's avatar
Sebastien2480
Copper Contributor
Jan 30, 2023
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
dscheikey's avatar
dscheikey
Bronze Contributor
Jan 30, 2023

Sebastien2480 

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,"")
)
  • Sebastien2480's avatar
    Sebastien2480
    Copper Contributor
    Jan 30, 2023
    Thanks 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

Resources