Extract a number from the data string in Cell

[abdulkharij]

Hi Dears,

I would like to ask if it's possible to extract a number 500005 to Cell C2 from the following data string in Cell A2 :

Cost Center: 4545544 Provo Earning: Miscellaneous (MISC) Employee: XXXXXXX Fund: 113 Sponsored Programs Job Profile: Assistant - BIOL. Line of Business: LOB002 Traditional Location: Main Campus Pay Group: Semi-Monthly Exempt Position: Assistant, Biology Program: 200 Sponsored Programs Run Category: Regular Simmons Grant: 454545 Davis Educ Fdtn-PLAN Prof Dev Spend Category: Salaries - Faculty Extra Comp (500005)

<tbody>
</tbody>

Thank you,
ABDEL

 

[Rick Rothstein]

One example in not much to go one, but assuming it is representative and that the number you want is always located at the end of the text and is always encased in parentheses...

=-TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

 

[Joe4]

Assuming that the number you want is always at the end and is in parentheses, you can use this formula (for an entry in cell A1):

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",255)),255)),")","")

 

[abdulkharij]

Thank you so much for your help. How about if I want to get the following :
Salaries - Faculty Extra Comp (500005)

One example in not much to go one, but assuming it is representative and that the number you want is always located at the end of the text and is always encased in parentheses...

=-TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

 

[Haluk]

Using Joe4's formula;

=TRIM(RIGHT(SUBSTITUTE(A1;":";REPT(" ";255));255))

Depending on your Excel settings, change semicolon characters (;) with comma (,)

i.e.;

=TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",255)),255))

 

[abdulkharij]

Thank you so much for your help. How about if I want to get the following :
Salaries - Faculty Extra Comp (500005)

 

[Rick Rothstein]

Thank you so much for your help. How about if I want to get the following :
Salaries - Faculty Extra Comp (500005)

Maybe this...

=MID(A1,SEARCH("Spend Category: ",A1)+16,300)

 

[abdulkharij]

Perfect Thank you so much for your great help

 

[admiral100]

Another option =RIGHT(A1,LEN(A1)-FIND("Salaries",A1)+1)

 

[Rick Rothstein]

Another option =RIGHT(A1,LEN(A1)-FIND("Salaries",A1)+1)

I would suspect that an item named "Spend Category" could start with words other than "Salaries".