Position of number or a character in a text string

[Analystbycuriosity]

I need to analyze a list of 200 such excel entries daily.
These are Text strings in each cell. Entire string is in Cell A1,A2,etc
for input '|' is character in the string.
A1: MANDAL FIN. CO. LTD SR- SD52 8.8 NCD 15JU27 FVRS10LAC|INE121A08OC|8.80|15-Jun-2027|100.250|100.2813|8.7360|500.00|-
A2: CORPORATION LTD. SR-I 7.69 LOA 16JN23 FVRS10LAC|INE029A0840|7.69|16-Jan-2023|100.00|100.0000|7.6841|1000.00|-
A3: ANAND FINANCE LIMITED SR-III BR NCD 24MY18 |INE093JCH6|0.00|24-May-2018|189.2240|189.2240|0.0000|25.00|-
A4: BANK LIMITED TRANCHE-1 SR-I 9 NCD 30DC21 FVRS50|INE092CK9|0.00|30-Dec-2021|103.2200|103.2200|8.0100|2.00|-
A5: HOUSING LIMITED SRIIICATIII&IV8.9NCD26SP21FV1000LOAUP25SP16|INE148IGF5|0.00|26-Sep-2021|101.1200|101.1200|8.5000|8.90|-
A6: RAILWAY CORPORATION LIMITED 6.88/7.38 BD 23MR23 FVRS1000 LOA UPTO 22MR13|INE0F07587|0.00|23-Mar-2023|103.2131|103.1691|6.1400|10000.00|-

I want below output in 5 columns for better analysis.

8.8 | MANDAL FIN. | 15-Jun-2027 | 8.736 | 500.00
7.69| CORPORATION LTD. | 16-Jan-2023 | 7.6841 | 1000.00
0 | ANAND FINANCE | 24-May-2018 | 0.0000 | 25.00
8.9 | BANK LIMITED | 26-Sep-2021 | 8.5000 | 8.90
6.88/7.38 | RAILWAY CORPORATION | 23-Mar-2023 | 6.1400 | 10000.00

For output '|' indicates different column and is only for presentation purpose.
numbers & dates in output should be in their respective formats so that filters can be used.

all kinds of solution will be appreciated
Regards!

 

[mikerickson]

Have you looked at TextToColumns?

 

[Youngdand]

Then use text to columns from the data tab, and choose delimited, and select other and enter the | which will split each string at the | mark.

 

[Analystbycuriosity]

Thanks Mikerickson & Youngdad. your solution is simplest and practical. I was trying all formulas etc.lol

1. There is a discontinuity in the data. How do i resolve it.
In cell A3 the value before the date is correct
in cell A4 & A5 instead of 0.00 it should be 9 & 8.9 ( see number before word NCD in string)
But the same number can be before a different word say BD in cell A6
Hence the First column in my output will miss some values and will show 0.00 instead

2. Is it possible to get the output in desired format

 

[Youngdand]

The Output is as per your text string.

HOUSING LIMITED SRIIICATIII&IV8.9NCD26SP21FV1000LOAUP25SP16|INE148IGF5|0.00|26-Sep-2021|101.1200|101.1200|8.5000|8.90|-

the | designates the column, and in column c there is only data in A1 and A2, the rest are 0.0, i can see that before the first | in a4 and a5 you have 9 and 8.9, but not at the 3rd | position as in rows a1 and a2.

You really need to look at how your data is formatted to begin with, and why the position of these is not standard across all rows.

Regards.

Dan.