How to Identify Number within text and show number as final result.

Hi All,

Can Someone help as to how can we Identify number within a Text Formatted Line item and move those numbers to a new line item with number as a format using some formula.

E.g.

Line item 1: Text Format : VV123BO

Line Item 2: Number Format : 123 ( the line item 2 should automatically identify number in Line item 1 and bring it in line item 2)

Regards,

Ronak

Accepted answers

All comments

Ronak123

@ Misbah ,
The data is totally unstructured where Length of characters within the text is not fixed.
Previously I had used MID function to arrive at final result where data length was fixed and it was working.

Is there any other way, where irrespective of Data length and number of characters , the formula just identify number in between the text and gives us the final result with those numbers

Misbah

@Ronak123

Is the format of the text same throughout? meaning if you know that the text is positioned at first two places and the last two ones then this should work

Misbah

@Ronak123

No, there is no direct function to take care of this - I wish there was.

If you could structure the texts - get the number sandwiched between texts. Try to keep one text character (minimum) at the front and the same number of text characters at the end. That way you should be able to get the number out of it.

ChrisAHeathcote

@Ronak123

The challenge here is to determine the start position and end position to use on a MID function so you can extract the number.

Therefore, create a list contain items 0 to 9. Add a top level, call it 'All Numbers'.Use a code that corresponds to the list name. Create a system module adding a line item and formula to retrieve the code.

Create a new module with this list, add two line items; start number and end number.

Use FIND function to search the data using the code of the new list as the criteria.

Set summary to min for start number and Max end number.

In you target module use SELECT to retrieve the top level star number and end number from this working module to use in a MID function.

ALKA

Thanks @ChrisAHeathcote , worked!

@Ronak123 , you should mark his solution as solved 🙂

Quick Links

Can you help?

Unanswered questions

Hide the cards under "Models" section once connected
Hi Is there a way to hide the cards under the "Models" section once connected, as I would like the users to see only the links to the apps? Thank you
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…