Need Help

[Dxe316]

Hi All,

I am looking to do the following:
A = The text i start with
B = would add the numbers that have a 14KW coming before it
C = Would show the number after RD
D = Would show the last number of the text string

A B C D 14KW-5.10GR. 96RD-.79 5RU-1.51 5.1 0.79 1.51 14KW-1.3GR. 14KW-.84GR. 24RD-.08 5S-.27 2.14 0.08 0.27 14KW-1.3GR. 14KW-.85GR. 24RD-.09 5RU-.29 2.15 0.09 0.29 <colgroup><col><col span="3"></colgroup><tbody> </tbody>

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

Any help would be greatly appreciated - thank you!

 

[istiasztalos]

Hi,

It can be a bit tricky without knowing what format variations the string could have....

Formula for B depends on how many time 14KW can occur

C can be difficult if we don't know how many digits can this number be or what character will follow after the number... in your example you had a space after the RD number in all 3 cases, so I assumed that we can use that as rule, this formula would find the RD tag and return the number between that and the next space character (wherein string is in A2)
=VALUE(MID(A2,SEARCH("RD-",A2)+3,SEARCH(" ",A2,SEARCH("RD-",A2))-SEARCH("RD-",A2)-3))

For D, this formula works if we can assume that all tags in your string will be followed by a - ( and it will never have a #, if you can have #, any other separator can be used)
This will get whatever is after the last - in your string
=VALUE(RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,"-","#",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))))

 

[Dxe316]

Wow Thanks a lot istiasztalos!

The formulas for C & D works great!


For B - KW can show up a maximum of 2 times.
Is there any ways to have the formula add the numbers after the KW and give me a total?

Hi,

It can be a bit tricky without knowing what format variations the string could have....

Formula for B depends on how many time 14KW can occur

C can be difficult if we don't know how many digits can this number be or what character will follow after the number... in your example you had a space after the RD number in all 3 cases, so I assumed that we can use that as rule, this formula would find the RD tag and return the number between that and the next space character (wherein string is in A2)
=VALUE(MID(A2,SEARCH("RD-",A2)+3,SEARCH(" ",A2,SEARCH("RD-",A2))-SEARCH("RD-",A2)-3))

For D, this formula works if we can assume that all tags in your string will be followed by a - ( and it will never have a #, if you can have #, any other separator can be used)
This will get whatever is after the last - in your string
=VALUE(RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,"-","#",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))))

 

[istiasztalos]

Hi Dxe,

If we know that 14KW may appear min 1 and max 2 times, this would work:
=VALUE(MID(A2,SEARCH("14KW-",A2)+5,SEARCH("GR",A2)-SEARCH("14KW-",A2)-5))+IFERROR(VALUE(MID(A2,SEARCH("14KW-",A2,SEARCH("14KW-",A2)+1)+5,SEARCH("GR",A2,SEARCH("GR",A2)+1)-SEARCH("14KW-",A2,SEARCH("14KW-",A2)+1)-5)),0)

Again, string is in A2, and it also assumes that the KW number will be followed by "GR", we need something to know when the number ends and in your examples there's always a "GR"