How can I take info from a cell and change it's format for a different cell?

[workinghere]

I have a cell that will display feet an inches as 10'0 or 7'0 and I need to take that information and display it in another cell in the exact format of 1000 or 0700. So I need to make sure the extra characters are included and the apostrophe is taken out. Is this possible?

Thank you very much.

 

[Joe4]

Welcome to the Board!

Are you entries of 10'0 actually Text entries, or numeric entries with Custom Formats?
If Custom Formats, what exactly is the Custom Format?

 

[Greg Truby]

To Joe's point, if the apostrophe is actually typed into the cell then:

=TEXT(LEFT(A1,FIND(CHAR(39),A1)-1),"00")&TEXT(RIGHT(A1,LEN(A1)-FIND(CHAR(39),A1)),"00")

If the apostrophe is due to some kind of a custom number format then we'd need to know more.

 

[workinghere]

Thank you!

It is just text. The format listed is still "General". It's taking the information from a drop-down menu on another sheet. I'm just trying to make it the right format so it will populate in another formula in a way I can copy-and-paste into our inventory program. It only recognizes it as 1000, 0810, etc.

 

[Joe4]

It is just text.

Then Greg's formula should work for you. Have you tried it out?

 

[workinghere]

Yes, it works perfectly! Thank you so much.

If you have some spare time, could you explain to me why this works? I'm not too familiar with the "LEN" or why the "39" is in there.

Again, thank you for the help.

 

[Joe4]

The LEN function just returns the length of a string (see: https://www.techonthenet.com/excel/formulas/len.php)

CHAR(39) is ASCII character 39, which is the single quote.
You could use:

FIND("'",A1)

in place of

FIND(CHAR(39),A1)

 

[Rick Rothstein]

To Joe's point, if the apostrophe is actually typed into the cell then:

=TEXT(LEFT(A1,FIND(CHAR(39),A1)-1),"00")&TEXT(RIGHT(A1,LEN(A1)-FIND(CHAR(39),A1)),"00")

If the apostrophe is due to some kind of a custom number format then we'd need to know more.

If I am not mistaken, I think this formula will also work...

=TEXT(SUBSTITUTE(A1,"'",":"),"hhmm")

 

[Greg Truby]

=TEXT(SUBSTITUTE(A1,"'",":"),"hhmm")

Sweet formula! I might tweak it slightly to handle measurements over 23'11"...

=TEXT(SUBSTITUTE(A1,CHAR(39),":"),"[hh]mm")


@ WorkingHere - as far as the CHAR(39) bit goes, 39 is the ASCII code for the single quote. When writing formulas that involve it, sometimes it can be tough to see the little guy. When I first read Rick's formula I thought he'd written a test for an empty string, i.e. "" instead of a test for a quote, i.e. "'". You can see that spotting the single apostrophe nestled between two double-quotes can take a bit of an eagle eye. By using CHAR(39) instead of "'", it just make the formula a bit easier to read (to me).

As far as what Rick's formula is doing, it's a slick little trick that can come in handy in certain situations, and it works nicely here. He is swapping the apostrophe out, replacing it with a colon, which then casts the value as a time in Excel's calc engine. He then formats the time without the colon in the middle. All I did was adjust the formatting on the hours so that measurements over "24 hours" wouldn't round into multiples of 24, since now we're formatting time values.

 

[Rick Rothstein]

Sweet formula!

Hey, thanks!

I might tweak it slightly to handle measurements over 23'11"...

=TEXT(SUBSTITUTE(A1,CHAR(39),":"),"[hh]mm")

Good tweak!!!