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

Ronak123
New Contributor

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

5 REPLIES 5
Ronak123
New Contributor

@ 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
Moderator

@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
Community Boss

@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. 

Chris
HeathcoteAndHerran.com
ALKA
Occasional Contributor

Thanks @ChrisAHeathcote , worked!

 

@Ronak123 , you should mark his solution as solved 🙂

Misbah
Moderator

@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_0-1635317770725.png

Misbah_1-1635317796794.png

 

Misbah