I'm cleaning my product database and I need to substrate text between a "#" and the space after this character to show an example:

ALMINE #6186 3 WAY EM/BRD --> I need to move the # 6186 at the end of the string.

JOY #197 BD LG GLOW RED --> Here I need to do the same.

Then I need to create a formula to subtract that one an put it at the end I was thinking into look for the space after the # but i can't.

Ill appreciate if you could help me with this.

Ty,

I subtract the character from #-1 to start then in that way I will have the title but ill need to create another formula to extract from the # to the space after the # space because the ID's length change.

CodePudding user response：

See the steps in the following image and the corresponding formulas below:

and in one formula is:

=REPLACE(A2,FIND("#",A2),FIND(" ",A2,B2)-FIND("#",A2),"") & " "  &  MID(A2,FIND("#",A2),FIND(" ",A2,B2)-FIND("#",A2))

Good luck!

CodePudding user response：

Under Office 365 (there is no tag excel version constraint in the question) you can play with TEXTBEFORE and TEXTAFTER functions to the get the expected result. Put in B1 the following formula:

=LET(text, A1:A2, prefix, TEXTBEFORE(text," "), suffix, TEXTAFTER(text," "), 
  number, TEXTBEFORE(suffix," "), prefix & TEXTAFTER(suffix, number) &" "& number)

Notes:

1. This approach doesn't require helper columns
2. It works for a single cell, but also for the Array version, there is no need to drag down the formula. It works for an entire range of data all at once.
3. LET is used for the reusability of the calculations.

and here is the output:

If you enter an