Forum Discussion

Copper Contributor

Jan 07, 2025

Extracting Text

Hi, for the life of me I cannot work out a way to extract the text I need,

I have an address eg. "2 Birch Court" and I want to extract the text to the format of "002BC"

But these addresses change to :

15 Oak Park

19 Birch Avenue

Ect.

Anyone have any ideas?

Diamond Contributor

As variant

=LET(
  split, TEXTSPLIT(A1," "),
  CONCAT( TEXT( TAKE(split,,1), "0000"), LEFT( DROP( split,, 1) ) )
)