Forum Discussion

Niels_NL's avatar
Niels_NL
Advocate I
10 years ago
Solved

Remove leading zero's from a string

Hey guys,   the situation is as follows: I have a set of values (string-type) that can either contain a number or the value 'AFW' (i.e.: unknown data). However, some numbers have leading zero's at ...
Need Help
  • sdjensen's avatar
    sdjensen
    10 years ago

    Hi,

     

    You could try something like this. This code will first check if the string can be converted into a number - if it can't then it will just return the string else it will convert the string to a number (removing leading zero's) and then turn the number back into a string.

     

    NumberXNoLeadingZero = 
IF(
	ISERROR( VALUE( 'Table'[NumberX] ) ),
	'Table'[NumberX],
	CONCATENATE( VALUE('Table'[NumberX]), "" )
)
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hello Sdjensen,

 

I have a similar issue to the one posted, but it has to do with removing leading zeros from each octet of an IP address. For example, I am getting IP address information int the following format:

 

062.045.162.150

 

and I need to convert it to:

 

62.45.162.150

 

How can I modify your example to get the result desired?

 

Thank you,

 

 

sdjensen's avatar
sdjensen
Solution Sage
9 years ago

Hi Anonymous,

 

I am sorry, but I haven't seen your question until now - did you solve it?

 

I the Query Editor I would use the Split by delimiter '.' into 4 columns and then for each column remote the leeding 0's and then Concatenate the 4 columns back into one column.