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
Answers
-
@ 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
0 -
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.
1 -
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.
3 -
1
