Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MikeD1314
Regular Visitor

Pulling all 10-digit from 1 Cell

Hello All,

 

Need help in extracting 10 digit strings from one cell.

 

Scope is an exported email box and needing to extract all 10-digit numbers from the Subject of the email(is collected within 1 cell in Excel). Found the below solution but need this to collect ALL of the occurrences, not just the first, and for each occurrence found it would need to make a new row. 

 

= List.Select(List.Transform(Text.Split([TextColumn], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) = 10){0}

 

Is this possible?

Thank you in advance. 😁

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

=List.Select(Text.SplitAny([Contents],Text.Remove([Contents],{"0".."9"})),each Text.Length(_)=10)

View solution in original post

7 REPLIES 7
wdx223_Daniel
Super User
Super User

=List.Select(Text.SplitAny([Contents],Text.Remove([Contents],{"0".."9"})),each Text.Length(_)=10)

Thank you very much! This works great, now from this point I should be able to extract each record in the list into a new row in the report!

ronrsnfld
Super User
Super User

Depending on the nature of your data, splitting text on a space may or may not return what you need. You could have a problem if there is punctuation after a value. You will obtain better answers if you post some data as text which can be copy/pasted, along with a screenshot of your expected results from that text.

v-jingzhang
Community Support
Community Support

Hi @MikeD1314 

 

If I understand your requirement correctly, you just need to remove {0} from the end of your previous code. This will give you the complete list rather than only the first item in the list. 

= List.Select(List.Transform(Text.Split([TextColumn], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) = 10)

Click the Expand icon on column header of the custom column and select "Expand to New Rows" option. 

vjingzhang_0-1674617215985.png

You will get below result then. Is this what you are after?

vjingzhang_1-1674617398730.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Thanks v-jingzhang. I've removed the "{0}" but isnt listing all of the items needed.

This is what I'm trying to get to, but want to learn along the way.

MikeD1314_0-1674666336129.png

 

 

Hi @MikeD1314 

Can you paste some sample data of the Contents column so I can use it for test? 

 

Einomi
Resolver II
Resolver II

Hi @MikeD1314 

 

Welcome to this wonderful Power BI community.

 

Just giving you a tip, if you want your posts to be answered. Post examples of your data and examples of what you expect as a outcome.

 

Pics are welcome.

 

Hope it helps

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors