cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

## Formula to trim the first number

Asteroid

Hello,

I have a column with 6 and 7 digits numbers. I want to remove the first digit form 7 digits number and leave 6 untouched

DATE               EXPECTED RESULTS

940729              940729

1020906            020906

940729              940729

1020906            020906

1020906            020906

940711              940711

Thanks

Bolide

Hi @EvansM, try this formula. ToNumber(IIF(Length(ToString([Date]))=6,[Date], Substring(ToString([Date]),1,6)))

Alteryx Certified Partner

Hi @EvansM

REGEX_Replace(ToString([Field1]), "\d?(\d{6})", "\$1")

Try that.

But I warn you that if you change your field to Numeric Type, this " 020906" will appear as "20906"

Cheers,

Asteroid

It works but is removing a zero (20906) and I want to  look like 020906 on the output

Asteroid

I need that first zero. Is very important the whole field needs to be 6 digits.

Thanks

Evans

Alteryx Certified Partner

Then you need to keep the field as a String.

Use the Regex I provided you and keep the field as V_WString and you'll be fine.

Cheers,

Pulsar

Hi @EvansM ,

A simple string of text formulas should do the trick.

Make sure that the field [Date] is string (I did this with a select tool, but you could also use tostring() in the formula

Try this formula for your [Expected Results] field, making sure that the output is a string so you don't lose leading zeros.

IF Length([Date])=6
THEN [Date]
ELSE Right([Date],6)
ENDIF

Will work for any [Date] strings over 6 characters.

Cheers!

Esther

Aurora

@EvansM  Similar to what @EstherB47  has, I would use this in a formula tool:

`Right(tostring(Date),6)`

Asteroid

Thanks a lot EstherB47 it works

Alteryx Certified Partner

My favorite solution here is @EstherB47 .

But ... (as the shoe falls),

I would just use:

`Right([Date],6)`

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels