Need help with a formula to transform values in a column

[Rnaval]

Hi All -

I have an excel data file with a column where I need a formula to transform the values. See below, I basically want just the hostname without the domain or blanks if there is no value in the cell.

Here's what the data should look like before and after the transform.

Before                                                                                          After

TrainingPC.emd.nyct.com  (delimiter is the first period)             TrainingPC

OperatorPC2  (there is no delimiter)                                           OperatorPC2

Blank Value (cell is blank)                                                            Blank Value

Appreciate any help with this.

Thanks...Rohit

[Rnaval]

Thank You...that worked quite well...

One more thing, how do I make the values all uppercase?

[techies · Accepted solution]

= if Text.Trim(Text.From([Hostnames])) = "" then "" else Text.Upper(Text.BeforeDelimiter(Text.From([Hostnames]), "."))

[v-saisrao-msft]

Hi @Rnaval ,
I wanted to check if you had the opportunity to review the information provided by @techies . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[techies]

Hi @Rnaval in the power query editor, you can add this custom column

= if [Hostnames] = null or Text.Trim([Hostnames]) = "" then "" else Text.BeforeDelimiter([Hostnames], ".")