0

I want to output a string from one cell into another cell. The string will always start with #0, and then there will be 4 to 7 characters following it. For example, for this cell:

UI-ESR25893-TA-03-0022 STEAMPLANT-#0009-R1 - Utility Tie-In Drawings-handled out of DRS - close

I'd want to output "0009-R1" to the new cell.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
abigidiot
  • 11
  • 2
  • You can do this using a regex in VBA. This answer should help you out: https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops. – Jud May 29 '20 at 16:03

1 Answers1

0

You can do this by using below formula where the example text resides in cell A1

=MID(A1,FIND("#0",A1,1)+1,FIND(" ",A1,FIND("#0",A1,1))-FIND("#0",A1,1)-1)

where,

FIND("#0",A1,1) locates position where #0 exists and then we add 1 to it to get rid of #.

FIND(" ",A1,FIND("#0",A1,1)) locates the first space after #0 occurs

FIND(" ",A1,FIND("#0",A1,1))-FIND("#0",A1,1)-1 works out the exact length of the string.

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27